表A
col1 col2 col3 col4
us1 aaa 1000 2002/5/2
us2 bbb 200 2002/5/2
us1 ccc -300 2002/5/3
us2 ddd -300 2002/5/3
us1 eee 500 2002/5/5
us1 eee -800 2002/5/7
查询得到
col1 col2 col3 col4 col5
us1 aaa 1000 2002/5/2 1000
us1 bbb -300 2002/5/3 700
us1 ccc 500 2002/5/5 1200
us1 eee -800 2002/5/7 400即每天统计(col3)都得到一个col5是以上天数的和.
sql 语句怎么来实现啊
谢谢
*,
col5=(Select SUM(col3) From A Where col1=T.col1 And col4<=T.col4)
From A T
*,
col5=(Select SUM(col3) From A Where col1=T.col1 And col4<=T.col4)
From A T
导出表方法
declare @t1 table (col1 varchar(10),col2 varchar(10),col3 int,col4 datetime)
insert @t1 select 'us1','aaa',1000,'2002/5/2'
union all select 'us2','bbb',200,'2002/5/2'
union all select 'us1','ccc',-300,'2002/5/3'
union all select 'us2','ddd',-300,'2002/5/3'
union all select 'us1','eee',500,'2002/5/5'
union all select 'us1','eee',-800,'2002/5/7'
select
*,
col5=(select sum(col3) from @t1 where col4<=A.col4 and col1=A.col1)
from @t1 A
where col1='us1'
*,
col5=(Select SUM(col3) From A Where col1=T.col1 And col4<=T.col4)
From A T ORDER BY col1, col4========================
select
*,
col5=(select sum(col3) from @t1 where col4<=A.col4 and col1=A.col1)
from @t1 A
where col1='us1' ORDER BY col4