求助：如何查询截止累积求和

[T-Share]

有数据如下：
daytime summoney
2011-5-1 10：30：00 3003.85
2011-5-2 11：30：00 2901.10
2011-5-7 01：58：00 8914.99
2011-5-9 10：30：00 6582.43
2011-5-3 06：31：00 3094.91
2011-5-1 19：21：00 6230.19

需要进行累积求和查询，例：按daytime排序，截止summoney总和达到16000（只需超过这个值），查询这个截止时间daytime

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([daytime] datetime,[summoney] numeric(6,2))
insert [tb]
select '2011-5-1 10:30:00',3003.85 union all
select '2011-5-2 11:30:00',2901.10 union all
select '2011-5-7 01:58:00',8914.99 union all
select '2011-5-9 10:30:00',6582.43 union all
select '2011-5-3 06:31:00',3094.91 union all
select '2011-5-1 19:21:00',6230.19
 
---查询---
declare @i int
set @i=16000
;with cte1 as
(select *,sm=(select sum(summoney) from tb where daytime<=t.daytime)
 from tb t
)
select daytime,summoney from cte1 
where daytime<=(select top 1 daytime from cte1 where sm>=@i order by daytime)
order by daytime

---结果---
daytime                 summoney
----------------------- ---------------------------------------
2011-05-01 10:30:00.000 3003.85
2011-05-01 19:21:00.000 6230.19
2011-05-02 11:30:00.000 2901.10
2011-05-03 06:31:00.000 3094.91
2011-05-07 01:58:00.000 8914.99

(5 行受影响)

如果只需要把时间查出来

declare @i int
set @i=16000
;with cte1 as
(select *,sm=(select sum(summoney) from tb where daytime<=t.daytime)
 from tb t
)
select top 1 daytime from cte1 
where daytime<=(select top 1 daytime from cte1 where sm>=@i order by daytime)
order by daytime desc

[longai123]

cte 为树 sql2005以上版本好像可以使用的。楼主用的是2000版本不支持

[coleling]

--借#1测试数据
--1.SQL 2000
    select daytime from tb t 
    where (select sum(summoney) from tb where daytime <= t.daytime) >= 16000 
        and (select sum(summoney) from tb where daytime <= t.daytime-1) < 16000)

/*
DAYTIME
-----------
2011-05-07 01:58:00.000
*/

--2.Oracle
select min(daytime) daytime from (select a.*,sum(summoney) over(order by daytime) total from tb1 a) where total > 16000;

/*
DAYTIME
-----------
2011-05-07 01:58:00.000
*/

[coleling]

晕，上面#5写错了一点

--借#1测试数据
--1.SQL 2000
select daytime from tb t 
where (select sum(summoney) from tb where daytime <= t.daytime) >= 16000 
    and (select sum(summoney) from tb where daytime < t.daytime) < 16000

/*
DAYTIME
-----------
2011-05-07 01:58:00.000
*/

--2.Oracle
select min(daytime) daytime from (select a.*,sum(summoney) over(order by daytime) total from tb1 a) where total > 16000;

/*
DAYTIME
-----------
2011-05-07 01:58:00.000
*/

[T-Share]

消息 156，级别 15，状态 1，第 5 行
在关键字 'with' 附近有语法错误。


为何显示错误？

[ForFumm]

SET NOCOUNT ON
DECLARE @t TABLE(
daytime DATETIME,
summoney NUMERIC(10,2))
INSERT INTO @t
SELECT 
'2011-5-1 10:30:00', 3003.85 UNION ALL SELECT
'2011-5-2 11:30:00', 2901.10 UNION ALL SELECT
'2011-5-7 01:58:00', 8914.99 UNION ALL SELECT
'2011-5-9 10:30:00', 6582.43 UNION ALL SELECT
'2011-5-3 06:31:00', 3094.91 UNION ALL SELECT
'2011-5-1 19:21:00', 6230.19
SET NOCOUNT OFF
SELECT TOP 1 A.daytime
FROM @t A WHERE   EXISTS ( SELECT * FROM @t  WHERE  daytime > A.daytime HAVING(SUM(summoney))>1600 ) ORDER BY A.daytime DESC
/*
daytime                                                
------------------------------------------------------ 
2011-05-07 01:58:00.000

（所影响的行数为 1 行）
*/