SQL2008数量统计问题

[qzhjh]

引用

按天统计数量，数量一天120为基数，按顺序合计一天数量超过120的数据就去除掉（数量不用刚刚好120，合计截止到
数量满足120那条数据之后的就不要了），如果是周末就统计到130。

原数据

日期 编号 数量
2016-10-07 00:00:00 10010001 86.70
2016-10-07 00:00:00 10010002 39.20
2016-10-07 00:00:00 10010003 140.00
2016-10-08 00:00:00 10010001 80.00
2016-10-08 00:00:00 10010002 85.00
2016-10-08 00:00:00 10010003 4.50
2016-10-09 00:00:00 10010001 20.00
2016-10-09 00:00:00 10010002 85.00
2016-10-09 00:00:00 10010003 50.00
2016-10-09 00:00:00 10010004 123.00


想要的结果

日期 编号 数量
2016-10-07 00:00:00 10010001 86.70
2016-10-07 00:00:00 10010002 39.20
2016-10-08 00:00:00 10010001 80.00
2016-10-08 00:00:00 10010002 85.00
2016-10-09 00:00:00 10010001 20.00
2016-10-09 00:00:00 10010002 85.00
2016-10-09 00:00:00 10010003 50.00

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([日期] Date,[编号] int,[数量] decimal(18,8))
Insert #T
select '2016-10-07',10010001,86.70 union all
select '2016-10-07',10010002,39.20 union all
select '2016-10-07',10010003,140.00 union all
select '2016-10-08',10010001,80.00 union all
select '2016-10-08',10010002,85.00 union all
select '2016-10-08',10010003,4.50 union all
select '2016-10-09',10010001,20.00 union all
select '2016-10-09',10010002,85.00 union all
select '2016-10-09',10010003,50.00 union all
select '2016-10-09',10010004,123.00
Go
SELECT * FROM (Select *,SUM([数量])OVER(PARTITION BY [日期] ORDER BY [编号] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Sum数量 from #T) AS t WHERE Sum数量-数量<120
/*
日期	编号	数量	Sum数量
2016-10-07	10010001	86.70000000	86.70000000
2016-10-07	10010002	39.20000000	125.90000000
2016-10-08	10010001	80.00000000	80.00000000
2016-10-08	10010002	85.00000000	165.00000000
2016-10-09	10010001	20.00000000	20.00000000
2016-10-09	10010002	85.00000000	105.00000000
2016-10-09	10010003	50.00000000	155.00000000
*/

[中国风]

SQL2012之前的版本这样用

SELECT * FROM (Select *,(SELECT SUM([数量]) FROM #t WHERE 日期=t.日期 AND [编号]<=t.[编号]) AS Sum数量 from #T AS t) AS t WHERE Sum数量-数量<120

[qzhjh]

SELECT * FROM (Select *,(SELECT SUM([数量]) FROM #t WHERE 日期=t.日期 AND [编号]<=t.[编号]) AS Sum数量 from #T AS t) AS t WHERE Sum数量-数量<120 你好！如果是周末数量要130，在帮忙改一下，谢谢