类似的例子: IF OBJECT_ID('ta') IS NOT NULL DROP TABLE dbo.ta GOCREATE TABLE ta ( ProductID INT NOT NULL PRIMARY KEY, ProductName NVARCHAR(32) ) INSERT INTO dbo.ta SELECT 1,N'围巾' UNION ALL SELECT 2,N'上衣' UNION ALL SELECT 3,N'连衣裙' UNION ALL SELECT 4,N'短裙' UNION ALL SELECT 5,N'长裤' UNION ALL SELECT 6,N'风衣' GOIF OBJECT_ID('tb') IS NOT NULL DROP TABLE dbo.tb GOCREATE TABLE TB ( AreaID INT NOT NULL PRIMARY KEY, Area NVARCHAR(32) ) INSERT INTO TB SELECT 1,N'上海' UNION ALL SELECT 2,N'广州' UNION ALL SELECT 3,N'香港' UNION ALL SELECT 4,N'大连' UNION ALL SELECT 5,N'重庆' GO IF OBJECT_ID('tc') IS NOT NULL DROP TABLE dbo.tc GOCREATE TABLE Tc ( ProductID INT, AreaID INT, Quantity INT ) ALTER TABLE tc ADD CONSTRAINT pk_ProductID FOREIGN KEY(ProductID) REFERENCES ta(ProductID) ALTER TABLE tc ADD CONSTRAINT pk_AreaID FOREIGN KEY(AreaID) REFERENCES tb(AreaID)INSERT INTO tc SELECT 2 , 5 , 150 UNION ALL SELECT 4 , 5 , 180 UNION ALL SELECT 5 , 5 , 50 UNION ALL SELECT 6 , 2 , 210 UNION ALL SELECT 3 , 5 , 330 UNION ALL SELECT 4 , 5 , 70 GOSELECT * FROM dbo.ta SELECT * FROM TB SELECT * FROM tcWITH t AS ( SELECT ProductID,AreaID,SUM(Quantity) 'Quantity' FROM tc GROUP BY ProductID,AreaID ) SELECT ta.ProductName N'产品名称',TB.Area N'销售地区',t.Quantity '销售总量' FROM t INNER JOIN ta ON t.ProductID = ta.ProductID INNER JOIN TB ON t.AreaID = TB.AreaID产品名称 销售地区 销售总量 -------------------------------- -------------------------------- ----------- 风衣 广州 210 上衣 重庆 150 连衣裙 重庆 330 短裙 重庆 250 长裤 重庆 50
是要这样吧select productname as 产品,area 地区 ,count(1)销售次数 from (select #A.ProductName,#B.area from #C left join #A on #C.productid =#A.id left join #B on #c.areaid=#B.areaid ) a group by productname,area产品 地区 销售次数 ---------- ---------- ----------- 长裤 大连 1 长裤 广州 1 上衣 广州 1 长裤 香港 1 短裙 香港 1 长裤 重庆 1
--A表:有1、2、3、4、5...10 这十种产品 --B表:有A/B/C/D/E五个地区 --C表:A表里面的十种产品分别在B表里面5个地区中的某几个销售过--楼主C表应该是保存的是:A表里面的十种产品分别在B表里面5个地区中销售情况 --C表: ID AreaID ProductID ProductNum -- 1 2 5 3 -- 1 3 4 2 -- 1 5 5 4 -- 1 7 2 5 -- 1 3 5 1 -- 1 4 5 6 --这样希望得到的结果应该是A表里面的产品在B表中5个地区的销售情况--ProductName Area1 Area2 Area3 Area4 Area5 -- a ? ? ? ? ? -- b ? ? ? ? ? -- c ? ? ? ? ? -- d ? ? ? ? ? -- e ? ? ? ? ? WITH tab_Product AS ( SELECT '1' AS ProductID , 'a' AS ProductName UNION ALL SELECT '2' AS ProductID , 'b' AS ProductName UNION ALL SELECT '3' AS ProductID , 'c' AS ProductName UNION ALL SELECT '4' AS ProductID , 'd' AS ProductName UNION ALL SELECT '5' AS ProductID , 'e' AS ProductName ), tab_Area AS ( SELECT '1' AS AreaID , '上海' AS AreaName UNION ALL SELECT '2' AS AreaID , '北京' AS AreaName UNION ALL SELECT '3' AS AreaID , '广州' AS AreaName UNION ALL SELECT '4' AS AreaID , '深圳' AS AreaName UNION ALL SELECT '5' AS AreaID , '杭州' AS AreaName ), tab_Traffic AS ( SELECT '2' AS AreaID , '5' AS ProductID , 3 AS ProductNum UNION ALL SELECT '3' AS AreaID , '4' AS ProductID , 2 AS ProductNum UNION ALL SELECT '5' AS AreaID , '5' AS ProductID , 5 AS ProductNum UNION ALL SELECT '1' AS AreaID , '2' AS ProductID , 7 AS ProductNum UNION ALL SELECT '3' AS AreaID , '5' AS ProductID , 10 AS ProductNum UNION ALL SELECT '4' AS AreaID , '5' AS ProductID , 5 AS ProductNum UNION ALL SELECT '3' AS AreaID , '2' AS ProductID , 20 AS ProductNum UNION ALL SELECT '3' AS AreaID , '2' AS ProductID , 13 AS ProductNum UNION ALL SELECT '4' AS AreaID , '3' AS ProductID , 4 AS ProductNum UNION ALL SELECT '5' AS AreaID , '1' AS ProductID , 6 AS ProductNum UNION ALL SELECT '5' AS AreaID , '2' AS ProductID , 3 AS ProductNum ) --SELECT a.ProductName , -- b.AreaName , -- c.ProductNum -- FROM tab_Product a , -- tab_Area b , -- tab_Traffic c -- WHERE c.AreaID = b.AreaID -- AND a.ProductID = c.ProductID SELECT * FROM ( SELECT a.ProductName , b.AreaName , c.ProductNum FROM tab_Product a , tab_Area b , tab_Traffic c WHERE c.AreaID = b.AreaID AND a.ProductID = c.ProductID ) t PIVOT ( SUM(t.ProductNum) FOR t.AreaName IN ( [上海], [北京], [广州], [深圳], [杭州] ) ) AS piv
同11楼一样,你要怎么样的结果格式。 是下面这样的格式吗? 长裤 A 10 长裤 B 20 长裤 C 30 短裙 A 10 短裙 B 20 短裙 C 30 短裙 E 555 风衣 A 130 风衣 B 320 风衣 C 320 风衣 D 40 风衣 E 555 连衣裙 C 30 连衣裙 D 40 连衣裙 E 33 上衣 A 10 上衣 B 20 上衣 D 40 上衣 E 555 围巾 A 10 围巾 B 20 围巾 C 30 围巾 D 40 围巾 E 555
就是要的这种效果,你太厉害了,还有一个问题,因为tab_Area这张表里面的数据是动态的,IN ( [上海], [北京], [广州],[深圳], [杭州] )这里面写成动态,请问怎么写? DECLARE @s NVARCHAR(4000) SELECT @s = ISNULL(@s + ',', '') + QUOTENAME([AreaName]) FROM #tab_Area GROUP BY [AreaName] EXEC ( 'select ProductName,'+@s+' from (SELECT a.ProductName , b.AreaName , CAST(c.ProductNum AS NUMERIC) ProductNum FROM #tab_Product a , #tab_Area b , #tab_Traffic c WHERE c.AreaID = b.AreaID AND a.ProductID = c.ProductID) t pivot (SUM(t.ProductNum) FOR t.AreaName IN ('+@s+'))AS piv') 把原with 语句用临时表#tab_Product a ,#tab_Area b ,#tab_Traffic c 代替,其他信息不变。具体数据的行专列可参考http://bbs.csdn.net/topics/240002706
1 围巾
2 上衣
3 连衣裙
4 短裙
5 长裤
6 风衣
....
B表: AreaID Area
1 上海
2 广州
3 香港
4 大连
5 重庆C表:ID AreaID ProductID
1 2 5
1 3 4
1 5 5
1 7 2
1 3 5
1 4 5
.....
1 围巾
2 上衣
3 连衣裙
4 短裙
5 长裤
6 风衣
....
B表: AreaID Area
1 上海
2 广州
3 香港
4 大连
5 重庆C表:ID AreaID ProductID
1 2 5
1 3 4
1 5 5
1 7 2
1 3 5
1 4 5
.....C表的ID怎么可以都是1呢?
1 围巾
2 上衣
3 连衣裙
4 短裙
5 长裤
6 风衣
....
B表: AreaID Area
1 上海
2 广州
3 香港
4 大连
5 重庆C表:ID AreaID ProductID
1 2 5
1 3 4
1 5 5
1 7 2
1 3 5
1 4 5
.....
请问,您C表中的ProductID引用的是A表中的ID,那么C表中的ID有什么意义
1 围巾
2 上衣
3 连衣裙
4 短裙
5 长裤
6 风衣
....
B表: AreaID Area
1 上海
2 广州
3 香港
4 大连
5 重庆C表:ID AreaID ProductID
1 2 5
1 3 4
1 5 5
1 7 2
1 3 5
1 4 5
.....
请问,您C表中的ProductID引用的是A表中的ID,那么C表中的ID有什么意义
ID没有特殊意义 就是一个自增字段
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE dbo.ta
GOCREATE TABLE ta
(
ProductID INT NOT NULL PRIMARY KEY,
ProductName NVARCHAR(32)
)
INSERT INTO dbo.ta
SELECT 1,N'围巾' UNION ALL
SELECT 2,N'上衣' UNION ALL
SELECT 3,N'连衣裙' UNION ALL
SELECT 4,N'短裙' UNION ALL
SELECT 5,N'长裤' UNION ALL
SELECT 6,N'风衣'
GOIF OBJECT_ID('tb') IS NOT NULL
DROP TABLE dbo.tb
GOCREATE TABLE TB
(
AreaID INT NOT NULL PRIMARY KEY,
Area NVARCHAR(32)
)
INSERT INTO TB
SELECT 1,N'上海' UNION ALL
SELECT 2,N'广州' UNION ALL
SELECT 3,N'香港' UNION ALL
SELECT 4,N'大连' UNION ALL
SELECT 5,N'重庆'
GO
IF OBJECT_ID('tc') IS NOT NULL
DROP TABLE dbo.tc
GOCREATE TABLE Tc
(
ProductID INT,
AreaID INT,
Quantity INT
)
ALTER TABLE tc ADD CONSTRAINT pk_ProductID FOREIGN KEY(ProductID) REFERENCES ta(ProductID)
ALTER TABLE tc ADD CONSTRAINT pk_AreaID FOREIGN KEY(AreaID) REFERENCES tb(AreaID)INSERT INTO tc
SELECT 2 , 5 , 150 UNION ALL
SELECT 4 , 5 , 180 UNION ALL
SELECT 5 , 5 , 50 UNION ALL
SELECT 6 , 2 , 210 UNION ALL
SELECT 3 , 5 , 330 UNION ALL
SELECT 4 , 5 , 70
GOSELECT * FROM dbo.ta
SELECT * FROM TB
SELECT * FROM tcWITH t AS (
SELECT ProductID,AreaID,SUM(Quantity) 'Quantity' FROM tc
GROUP BY ProductID,AreaID
)
SELECT ta.ProductName N'产品名称',TB.Area N'销售地区',t.Quantity '销售总量'
FROM t INNER JOIN ta ON t.ProductID = ta.ProductID
INNER JOIN TB ON t.AreaID = TB.AreaID产品名称 销售地区 销售总量
-------------------------------- -------------------------------- -----------
风衣 广州 210
上衣 重庆 150
连衣裙 重庆 330
短裙 重庆 250
长裤 重庆 50
from
(select #A.ProductName,#B.area
from #C left join #A on #C.productid =#A.id
left join #B on #c.areaid=#B.areaid ) a
group by productname,area产品 地区 销售次数
---------- ---------- -----------
长裤 大连 1
长裤 广州 1
上衣 广州 1
长裤 香港 1
短裙 香港 1
长裤 重庆 1
--B表:有A/B/C/D/E五个地区
--C表:A表里面的十种产品分别在B表里面5个地区中的某几个销售过--楼主C表应该是保存的是:A表里面的十种产品分别在B表里面5个地区中销售情况
--C表: ID AreaID ProductID ProductNum
-- 1 2 5 3
-- 1 3 4 2
-- 1 5 5 4
-- 1 7 2 5
-- 1 3 5 1
-- 1 4 5 6
--这样希望得到的结果应该是A表里面的产品在B表中5个地区的销售情况--ProductName Area1 Area2 Area3 Area4 Area5
-- a ? ? ? ? ?
-- b ? ? ? ? ?
-- c ? ? ? ? ?
-- d ? ? ? ? ?
-- e ? ? ? ? ?
WITH tab_Product
AS ( SELECT '1' AS ProductID ,
'a' AS ProductName
UNION ALL
SELECT '2' AS ProductID ,
'b' AS ProductName
UNION ALL
SELECT '3' AS ProductID ,
'c' AS ProductName
UNION ALL
SELECT '4' AS ProductID ,
'd' AS ProductName
UNION ALL
SELECT '5' AS ProductID ,
'e' AS ProductName
),
tab_Area
AS ( SELECT '1' AS AreaID ,
'上海' AS AreaName
UNION ALL
SELECT '2' AS AreaID ,
'北京' AS AreaName
UNION ALL
SELECT '3' AS AreaID ,
'广州' AS AreaName
UNION ALL
SELECT '4' AS AreaID ,
'深圳' AS AreaName
UNION ALL
SELECT '5' AS AreaID ,
'杭州' AS AreaName
),
tab_Traffic
AS ( SELECT '2' AS AreaID ,
'5' AS ProductID ,
3 AS ProductNum
UNION ALL
SELECT '3' AS AreaID ,
'4' AS ProductID ,
2 AS ProductNum
UNION ALL
SELECT '5' AS AreaID ,
'5' AS ProductID ,
5 AS ProductNum
UNION ALL
SELECT '1' AS AreaID ,
'2' AS ProductID ,
7 AS ProductNum
UNION ALL
SELECT '3' AS AreaID ,
'5' AS ProductID ,
10 AS ProductNum
UNION ALL
SELECT '4' AS AreaID ,
'5' AS ProductID ,
5 AS ProductNum
UNION ALL
SELECT '3' AS AreaID ,
'2' AS ProductID ,
20 AS ProductNum
UNION ALL
SELECT '3' AS AreaID ,
'2' AS ProductID ,
13 AS ProductNum
UNION ALL
SELECT '4' AS AreaID ,
'3' AS ProductID ,
4 AS ProductNum
UNION ALL
SELECT '5' AS AreaID ,
'1' AS ProductID ,
6 AS ProductNum
UNION ALL
SELECT '5' AS AreaID ,
'2' AS ProductID ,
3 AS ProductNum
)
--SELECT a.ProductName ,
-- b.AreaName ,
-- c.ProductNum
-- FROM tab_Product a ,
-- tab_Area b ,
-- tab_Traffic c
-- WHERE c.AreaID = b.AreaID
-- AND a.ProductID = c.ProductID
SELECT *
FROM ( SELECT a.ProductName ,
b.AreaName ,
c.ProductNum
FROM tab_Product a ,
tab_Area b ,
tab_Traffic c
WHERE c.AreaID = b.AreaID
AND a.ProductID = c.ProductID
) t PIVOT ( SUM(t.ProductNum) FOR t.AreaName IN ( [上海], [北京], [广州],
[深圳], [杭州] ) ) AS piv
是下面这样的格式吗?
长裤 A 10
长裤 B 20
长裤 C 30
短裙 A 10
短裙 B 20
短裙 C 30
短裙 E 555
风衣 A 130
风衣 B 320
风衣 C 320
风衣 D 40
风衣 E 555
连衣裙 C 30
连衣裙 D 40
连衣裙 E 33
上衣 A 10
上衣 B 20
上衣 D 40
上衣 E 555
围巾 A 10
围巾 B 20
围巾 C 30
围巾 D 40
围巾 E 555
DECLARE @s NVARCHAR(4000)
SELECT @s = ISNULL(@s + ',', '') + QUOTENAME([AreaName])
FROM #tab_Area
GROUP BY [AreaName]
EXEC ( 'select ProductName,'+@s+' from (SELECT a.ProductName ,
b.AreaName ,
CAST(c.ProductNum AS NUMERIC) ProductNum
FROM #tab_Product a ,
#tab_Area b ,
#tab_Traffic c
WHERE c.AreaID = b.AreaID
AND a.ProductID = c.ProductID) t
pivot (SUM(t.ProductNum) FOR t.AreaName IN ('+@s+'))AS piv') 把原with 语句用临时表#tab_Product a ,#tab_Area b ,#tab_Traffic c 代替,其他信息不变。具体数据的行专列可参考http://bbs.csdn.net/topics/240002706