有一SALE表,記錄如下: 客戶 銷售月份 銷售金額
A 200605 100
B 200605 200
C 200605 80
D 200605 170 A 200606 220
B 200606 200
C 200606 90 A 200607 200
B 200607 300
C 200607 70
D 200607 400 想要這樣的結果: 客戶 200607 7月排行 200606 6月排行 200605 5月排行
D 400 1 170 2
B 300 2 200 2 200 1
A 200 3 220 1 100 3
C 70 4 90 3 80 4
不知有沒有執行效率比較高的寫法? 先謝謝!
A 200605 100
B 200605 200
C 200605 80
D 200605 170 A 200606 220
B 200606 200
C 200606 90 A 200607 200
B 200607 300
C 200607 70
D 200607 400 想要這樣的結果: 客戶 200607 7月排行 200606 6月排行 200605 5月排行
D 400 1 170 2
B 300 2 200 2 200 1
A 200 3 220 1 100 3
C 70 4 90 3 80 4
不知有沒有執行效率比較高的寫法? 先謝謝!
sum(case when 銷售月份='200606' then 銷售金額 end) as [200606],
sum(case when 銷售月份='200605' then 銷售金額 end) as [200605]
into #t
from SALE
group by 客戶select 客戶,[200607],(select count(*) from #t where [200607]>=a.[200607]) as [7月排行],
[200606],(select count(*) from #t where [200606]>=a.[200606]) as [6月排行],
[200605],(select count(*) from #t where [200605]>=a.[200605]) as [5月排行]
from #t adrop table #t
CREATE TABLE SALE(客戶 char(1), 銷售月份 int, 銷售金額 int)
INSERT SALE SELECT 'A', 200605, 100
UNION ALL SELECT 'B', 200605, 200
UNION ALL SELECT 'C', 200605, 80
UNION ALL SELECT 'D', 200605, 170UNION ALL SELECT 'A', 200606, 220
UNION ALL SELECT 'B', 200606, 200
UNION ALL SELECT 'C', 200606, 90UNION ALL SELECT 'A', 200607, 200
UNION ALL SELECT 'B', 200607, 300
UNION ALL SELECT 'C', 200607, 70
UNION ALL SELECT 'D', 200607, 400
GO-- SQL 2005
SELECT 客戶,
[200605], [200605排行] = ROW_NUMBER() OVER(ORDER BY [200605] DESC),
[200606], [200606排行] = ROW_NUMBER() OVER(ORDER BY [200606] DESC),
[200607], [200607排行] = ROW_NUMBER() OVER(ORDER BY [200607] DESC)
FROM SALE
PIVOT(
SUM(銷售金額)
FOR 銷售月份 IN([200605], [200606], [200607])
)P
ORDER BY [200605排行]
GODROP TABLE SALE-- 结果:
客戶 200605 200605排行 200606 200606排行 200607 200607排行
---- ----------- -------------------- ----------- -------------------- ----------- --------------------
B 200 1 200 2 300 2
D 170 2 NULL 4 400 1
A 100 3 220 1 200 3
C 80 4 90 3 70 4(4 行受影响)
SELECT 客戶,
[200607],
[200607排行] = CASE
WHEN [200607] IS NULL THEN NULL
ELSE ROW_NUMBER() OVER(ORDER BY [200607] DESC) END,
[200606],
[200606排行] = CASE
WHEN [200606] IS NULL THEN NULL
ELSE ROW_NUMBER() OVER(ORDER BY [200606] DESC) END,
[200605],
[200605排行] = CASE
WHEN [200605] IS NULL THEN NULL
ELSE ROW_NUMBER() OVER(ORDER BY [200605] DESC) END
FROM SALE
PIVOT(
SUM(銷售金額)
FOR 銷售月份 IN([200605], [200606], [200607])
)P
ORDER BY [200607排行], [200606排行], [200605排行]
insert t select 'A', '200605', 100
union all select 'B', '200605', 200
union all select 'C', '200605', 80
union all select 'D', '200605', 170union all select 'A', '200606', 220
union all select 'B', '200606', 200
union all select 'C', '200606', 90union all select 'A', '200607', 200
union all select 'B', '200607', 300
union all select 'C', '200607', 70
union all select 'D', '200607', 400declare @s nvarchar(4000)
--------------------------------------------------------------------------
set @s=N'create table dataTable(客戶 char(1)'
select @s=+@s+',Col'+銷售月份+' int' from
(
select distinct 銷售月份 from t
) a order by 銷售月份 desc
set @s=@s+')'
exec(@s)
------------------------------------------------------------------------
set @s=' insert into dataTable select 客戶, sum(case 銷售月份 when '
select @s=@s+''''+銷售月份+''' then 銷售金額 else 0 end) ['+銷售月份+'],'
+'sum(case 銷售月份 when' from
(
select distinct 銷售月份 from t
) a order by 銷售月份 desc
set @s=stuff(@s,len(@s)-18,19,'')+' from t group by 客戶'
exec (@s)select * from dataTabledrop table t
drop table dataTable/*
客戶 Col200607 Col200606 Col200605
---- ----------- ----------- -----------
A 200 220 100
B 300 200 200
C 70 90 80
D 400 0 170
*/