這兩天協助客戶處理累積交易餘額的資料修補,因為要使用到累積總計(Running Totals),但客戶還在使用SQL Server 2008 R2,筆記SQL Server 2012前後兩個版本的作法。
我們先用CTE準備8筆簡單測試資料
WITH Hero(name, ModifiedDate, Score)
AS (SELECT 'David','2015-01-01',30 UNION ALL
SELECT 'David','2016-01-01',20 UNION ALL
SELECT 'Charlemagne','2013-01-01',10 UNION ALL
SELECT 'Charlemagne','2014-01-01',20 UNION ALL
SELECT 'Charlemagne','2015-01-01',30 UNION ALL
SELECT 'Charlemagne','2016-01-01',40 UNION ALL
SELECT 'Caesar','2015-01-01',40 UNION ALL
SELECT 'Caesar','2016-01-01',30)
SELECT * from Hero
需求是每一筆資料列後面按照撲克牌老K人物為單位加上一個資料行(累積小計),像是這樣:
SQL Server 2012以後的寫法(window function):
WITH Hero(name, ModifiedDate, Score)
AS (SELECT 'David','2015-01-01',30 UNION ALL
SELECT 'David','2016-01-01',20 UNION ALL
SELECT 'Charlemagne','2013-01-01',10 UNION ALL
SELECT 'Charlemagne','2014-01-01',20 UNION ALL
SELECT 'Charlemagne','2015-01-01',30 UNION ALL
SELECT 'Charlemagne','2016-01-01',40 UNION ALL
SELECT 'Caesar','2015-01-01',40 UNION ALL
SELECT 'Caesar','2016-01-01',30)
SELECT A.name,A.ModifiedDate,A.Score,SUM(a.Score) OVER (PARTITION BY a.name ORDER BY a.ModifiedDate) AS 'total'
FROM Hero a
ORDER BY A.name,A.ModifiedDate
執行結果:
SQL Server 2008 R2以前的寫法1(Self Join)
WITH Hero(name, ModifiedDate, Score)
AS (SELECT 'David','2015-01-01',30 UNION ALL
SELECT 'David','2016-01-01',20 UNION ALL
SELECT 'Charlemagne','2013-01-01',10 UNION ALL
SELECT 'Charlemagne','2014-01-01',20 UNION ALL
SELECT 'Charlemagne','2015-01-01',30 UNION ALL
SELECT 'Charlemagne','2016-01-01',40 UNION ALL
SELECT 'Caesar','2015-01-01',40 UNION ALL
SELECT 'Caesar','2016-01-01',30)
SELECT A.name,A.ModifiedDate,A.Score,SUM(B.Score) AS RUNNING_TOTAL
FROM Hero A JOIN Hero B
ON A.name = B.name -- Partition column
AND A.ModifiedDate >= B.ModifiedDate -- condition column
GROUP BY A.name,A.ModifiedDate,A.Score
ORDER BY A.name,A.ModifiedDate
執行結果:
SQL Server 2008 R2以前的寫法2(Sub Query)
(同事k提供)
WITH Hero(name, ModifiedDate, Score)
AS (SELECT 'David','2015-01-01',30 UNION ALL
SELECT 'David','2016-01-01',20 UNION ALL
SELECT 'Charlemagne','2013-01-01',10 UNION ALL
SELECT 'Charlemagne','2014-01-01',20 UNION ALL
SELECT 'Charlemagne','2015-01-01',30 UNION ALL
SELECT 'Charlemagne','2016-01-01',40 UNION ALL
SELECT 'Caesar','2015-01-01',40 UNION ALL
SELECT 'Caesar','2016-01-01',30)
SELECT a.*, (SELECT SUM(b.Score) FROM Hero b WHERE a.name = b.name and b.ModifiedDate <= a.ModifiedDate) AS 'total'
FROM Hero a
ORDER BY A.name,A.ModifiedDate
執行結果:
參考:
Calculating Running Totals using SQL