SQL按日期计算数据的总量

[Harvey_He]

oracle
一张表简单如下：
datatime（类型是datatime类型） counts（number类型）
2009-8-7 00:00:00 21
2009-8-8 00:00:00 32
2009-8-9 00:00:00 51
..... ....

我想查看每天的总量，请问SQL怎么写？结果如下：
datatime（类型是datatime类型） counts（number类型） total
2009-8-7 00:00:00 21 21
2009-8-8 00:00:00 32 53
2009-8-9 00:00:00 51 104
...... ... ....

[lovexilove]

帮顶

[bancxc]

select *,(select sum(counts) from tb where datetime<=t.datetime ) as total
from tb t

[dp517849241]

...

[bancxc]

select a.datetime,a.counts,sum(b.counts) as total
from tb a,tb b
where a.datetime>=b.datetime
group by a.datetime,a.counts

[mbh0210]

declare @t table( datatime datetime, counts int )
insert into @t values('2009-8-7 00:00:00'        ,      21 )
insert into @t values('2009-8-8 00:00:00'        ,      32 )
insert into @t values('2009-8-9 00:00:00'        ,      51 )

select a.*,
(select sum( counts) from @t where datatime <= a.datatime) as total
 from @t a

[kensouterry]

ORACLE的不会，以前好像用过一次，n不方便。

[ws_hgo]

  date datetime,
  counts int
)
insert into #YY select '2009-8-7 00:00:00',21
insert into #YY select '2009-8-8 00:00:00',32
insert into #YY select '2009-8-9 00:00:00',51

select date,counts,
       (select sum(counts) from #YY where date<=Y.date) total
from  #YY Y
date                    counts      total
----------------------- ----------- -----------
2009-08-07 00:00:00.000 21          21
2009-08-08 00:00:00.000 32          53
2009-08-09 00:00:00.000 51          104

[ws_hgo]

create table #YY
(
  date datetime,
  counts int
)
insert into #YY select '2009-8-7 00:00:00',21
insert into #YY select '2009-8-8 00:00:00',32
insert into #YY select '2009-8-9 00:00:00',51

select date,counts,
       (select sum(counts) from #YY where date<=Y.date) total
from  #YY Y

[Harvey_He]

[Quote=引用 4 楼 bancxc 的回复:]
select a.datetime,a.counts,sum(b.counts) as total
from tb a,tb b
where a.datetime>=b.datetime
group by a.datetime,a.counts
[/Quote]


刚才没说清楚：有两个字段没写出来
进行sum的时候要用另外的两个字段限定：type，class

[bancxc]

..难道你一点sql不会


select a.datetime,a.counts,sum(b.counts) as total
from tb a,tb b
where a.datetime>=b.datetime and a.type=b.type and a.class=b.class
group by a.datetime,a.counts

[Harvey_He]

[Quote=引用 10 楼 bancxc 的回复:]
..难道你一点sql不会


select a.datetime,a.counts,sum(b.counts) as total
from tb a,tb b
where a.datetime>=b.datetime and a.type=b.type and a.class=b.type
group by a.datetime,a.counts
[/Quote]

是按照type ，type 两个条件group by的，替换了不行

[ws_hgo]

select date,counts,type,class, 
       (select sum(counts) from #YY where date<=Y.date) total
from  #YY Y
group by date,counts,type,class

[justwannaloveni]

select convert(varchar(10),datatime,120),counts,count(*) from oracle group by convert(varchar(10),datatime,120),counts