sql，求和小于一定值的数据行解决方法

2012-06-07

sql，求和小于一定值的数据行求SQL 写法:表 t字段ID, 字段V03172438410求 V 累计到 20 的记录效果如:select

sql，求和小于一定值的数据行
求SQL 写法:
表 t
字段ID, 字段V
0 3
1 7
2 4
3 8
4 10

求 V 累计到 20 的记录
效果如:
select * from t where sum(v) < 20 order by ID

不要用存储过程，如何构建SQL

[解决办法]

SQL code

if object_id('[t]') is not null drop table [t]gocreate table [t]([ID] int,[V] int)insert [t]select 0,3 union allselect 1,7 union allselect 2,4 union allselect 3,8 union allselect 4,10goselect *from t awhere (select sum(v) from t where t.id<=a.id)<20order by id/**ID          V----------- -----------0           31           72           4(3 行受影响)**/
[解决办法]
SQL codeIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOCREATE TABLE tba(    ID INT,    Value INT)GOINSERT INTO tbaSELECT 0, 3 UNIONSELECT 1, 7 UNIONSELECT 2, 4 UNIONSELECT 3, 8 UNIONSELECT 4, 10SELECT ID,valueFROM tba AS AWHERE (SELECT SUM(value) FROM tba WHERE ID <= A.ID) < 20ID    value0    31    72    4--如果要累计的效果SELECT ID,value,(SELECT SUM(value) FROM tba WHERE ID <= A.ID) AS totalFROM tba AS AID    value    total0    3    31    7    102    4    143    8    224    10    32

热点排行

SQL Server

sql，求和小于一定值的数据行解决方法