表1、
Id name time
13 h 2009-09-30
14 i 2009-09-30
15 j 2009-09-30
16 k 2009-09-30
1 a 2009-10-01
2 b 2009-10-01
3 c 2009-10-02
4 d 2009-10-03
5 e 2009-10-03
6 f 2009-10-03
7 g 2009-10-04
结果
time daycounts counts
2009-09-30 4(新增) (包括这个日期之前的所有人数)
2009-10-01 2 6
2009-10-02 1 7
2009-10-03 3 10
2009-10-04 1 11
Id name time
13 h 2009-09-30
14 i 2009-09-30
15 j 2009-09-30
16 k 2009-09-30
1 a 2009-10-01
2 b 2009-10-01
3 c 2009-10-02
4 d 2009-10-03
5 e 2009-10-03
6 f 2009-10-03
7 g 2009-10-04
结果
time daycounts counts
2009-09-30 4(新增) (包括这个日期之前的所有人数)
2009-10-01 2 6
2009-10-02 1 7
2009-10-03 3 10
2009-10-04 1 11
试试这个
select t.time,count, sum(count) over( order by time)
from (select time,count(*) count from test group by time) t
from table1
group by time
我还在那捣鼓了10几分钟没捣鼓出来
帮你试了一下
SQL> select * from t1; ID NAME TIME
---------- ---------- ----------
1 a 2009-09-30
2 b 2009-09-30
3 c 2009-09-29
4 d 2009-09-28SQL> select time,count(1)daycounts,sum(count(1))over(order by time) counts
2 from t1
3 group by time;TIME DAYCOUNTS COUNTS
---------- ---------- ----------
2009-09-28 1 1
2009-09-29 1 2
2009-09-30 2 4SQL>
结果如下
time daycounts counts
2009-09-30 4(新增) (包括这个日期之前的所有人数)
2009-10-01 2 6
2009-10-02 1 7
2009-10-03 3 10
2009-10-04 1 11
周累计 11 11
···· ···· ····
···· ···· ····
···· ···· ····
月累计 ···· ····
from table1
group by rollup(to_char(time,'mm'))
varchar型