sql 循环查询问题 解决追加100

declare @T table([会员号] nvarchar(3),[交易号] int,[交易金额] int,[交易时间] Datetime)Insert @Tselect N'001',11,1200,'2009-10-01' union allselect N'001',32,2000,'2009-10-02' union allselect N'001',30,500,'2009-10-03' union allselect N'006',52,2000,'2009-10-02' union allselect N'006',212,400,'2009-10-03' union allselect N'003',61,500,'2009-10-05' union allselect N'003',32,1550,'2009-10-06' union allselect N'003',33,300,'2009-10-10' union allselect N'007',29,320,'2009-10-21' union allselect N'007',31,520,'2009-10-12' union allselect N'007',93,1000,'2009-10-11' union allselect N'007',21,250,'2009-10-24' union allselect N'007',35,1000,'2009-10-09' --SQL2000SELECT *FROM @T AS xWHERE [交易时间]=(Select MIN([交易时间]) from @T AS a WHERE (SELECT SUM([交易金额]) FROM @T WHERE [会员号]=a.[会员号] AND [交易时间]<=a.[交易时间])>=2000 AND [会员号]=x.[会员号] ) --SQL2005SELECT [会员号],[交易号],[交易金额],[交易时间]FROM (Select *,row=ROW_NUMBER()OVER(PARTITION BY [会员号] ORDER BY 交易时间) from @T AS a WHERE (SELECT SUM([交易金额]) FROM @T WHERE [会员号]=a.[会员号] AND [交易时间]<=a.[交易时间])>=2000)tWHERE row=1/*会员号 交易号 交易金额 交易时间---- ----------- ----------- -----------------------001 32 2000 2009-10-02 00:00:00.000003 32 1550 2009-10-06 00:00:00.000006 52 2000 2009-10-02 00:00:00.000007 93 1000 2009-10-11 00:00:00.000(4 个资料列受到影响)*/ 收起