if object_id('pubs..t') is not null drop table t gocreate table t ( a datetime, b int )insert into t(a,b) values('20061105',10) insert into t(a,b) values('20061106',12) insert into t(a,b) values('20061107',13) insert into t(a,b) values('20061108',14) insert into t(a,b) values('20061109',15)select convert(varchar(10),a,120) as a,b,(select sum(b) from t where a<=m.a) as 结果 from t m drop table ta b 结果 ---------- ----------- ----------- 2006-11-05 10 10 2006-11-06 12 22 2006-11-07 13 35 2006-11-08 14 49 2006-11-09 15 64(所影响的行数为 5 行)
实际上就是COUNT计数法.只不过将COUNT换成SUM而已本质一样.
(select A,sum(B) from tablename where A=20061105 and B=10) union (select A,sum(B) from tablename where A=20061105 or A=20061106) union (select A,sum(B) from tablename where A=20061105 or A=20061106 or A=20061107) union (select A,sum(B) from tablename where A=20061105 or A=20061106 or A=20061107 or A=20061108) union (select A,sum(B) from tablename where A=20061105 or A=20061106 or A=20061107 or A=20061108 or A=20061109) ?????
update 表名 set B=(select sum(t2.B) from 表名 t1,表名 t2 where t2.A>t1.A)
创建表:create table Test (A char(8),B int) 插入数据:insert into Test4 values ('20061105',10) insert into Test4 values ('20061106',12) insert into Test4 values ('20061107',13) insert into Test4 values ('20061108',14) insert into Test4 values ('20061109',15) SQL语句:select A,B=(select sum(B) from Test where A<=t.A) from Test as t MS SQL Server2000下测试通过
group by a
不知道是不是这意思
drop table t
gocreate table t
(
a datetime,
b int
)insert into t(a,b) values('20061105',10)
insert into t(a,b) values('20061106',12)
insert into t(a,b) values('20061107',13)
insert into t(a,b) values('20061108',14)
insert into t(a,b) values('20061109',15)select convert(varchar(10),a,120) as a,b,(select sum(b) from t where a<=m.a) as 结果 from t m
drop table ta b 结果
---------- ----------- -----------
2006-11-05 10 10
2006-11-06 12 22
2006-11-07 13 35
2006-11-08 14 49
2006-11-09 15 64(所影响的行数为 5 行)
where A=20061105 and B=10)
union
(select A,sum(B) from tablename
where A=20061105 or A=20061106)
union
(select A,sum(B) from tablename
where A=20061105 or A=20061106 or A=20061107)
union
(select A,sum(B) from tablename
where A=20061105 or A=20061106 or A=20061107 or A=20061108)
union
(select A,sum(B) from tablename
where A=20061105 or A=20061106 or A=20061107 or A=20061108 or A=20061109)
?????
插入数据:insert into Test4 values ('20061105',10)
insert into Test4 values ('20061106',12)
insert into Test4 values ('20061107',13)
insert into Test4 values ('20061108',14)
insert into Test4 values ('20061109',15)
SQL语句:select A,B=(select sum(B) from Test where A<=t.A) from Test as t
MS SQL Server2000下测试通过