统计拉链数据

[khyhli]

统计某商店的营业额。
date sale
1 20
2 15
3 14
4 18
5 30
规则：按天统计：每天都统计前面几天的总额
得到的结果：
DATE SALE SUM
----- -------- ------
1 20 20 --1天
2 15 35 --1天＋2天
3 14 49 --1天＋2天＋3天
4 18 67 .
5 30 97 .

[khyhli]

select date,sale,sum(sale) over (order by dates) as sum from table

[中国风]

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([date] int,[sale] int)
Insert #T
select 1,20 union all
select 2,15 union all
select 3,14 union all
select 4,18 union all
select 5,30
Go
Select *,(SELECT SUM([sale]) FROM #T WHERE [date]<=a.[date]) AS [SUM] FROM #T AS a
/*
date	sale	SUM
1	20	20
2	15	35
3	14	49
4	18	67
5	30	97*/

[中国风]

SQL 2012以上的版本，可这样

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([date] int,[sale] int)
Insert #T
select 1,20 union all
select 2,15 union all
select 3,14 union all
select 4,18 union all
select 5,30
Go
Select *,SUM(sale)OVER(ORDER BY date ASC RANGE BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW) AS [SUM] FROM #T
/*
date	sale	SUM
1	20	20
2	15	35
3	14	49
4	18	67
5	30	97*/