22,206
社区成员
发帖
与我相关
我的任务
分享
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--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
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
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE dbo.ta
GO
CREATE 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'风衣'
GO
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE dbo.tb
GO
CREATE 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
GO
CREATE 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
GO
SELECT * FROM dbo.ta
SELECT * FROM TB
SELECT * FROM tc
WITH 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