有一个表t(c_date date,id varchar2(30)
数据
c_date id
2011-05-20 11:00:01 1
2011-05-20 11:00:02 2
2011-05-20 11:01:00 3
2011-05-20 11:02:00 4
2011-05-20 11:03:00 5结果为: c_date count(id)???
2011-05-20 11:00:00 2
2011-05-20 11:01:00 3
2011-05-20 11:02:00 4
2011-05-20 11:03:00 5
请问这个怎么搞定呀。
数据
c_date id
2011-05-20 11:00:01 1
2011-05-20 11:00:02 2
2011-05-20 11:01:00 3
2011-05-20 11:02:00 4
2011-05-20 11:03:00 5结果为: c_date count(id)???
2011-05-20 11:00:00 2
2011-05-20 11:01:00 3
2011-05-20 11:02:00 4
2011-05-20 11:03:00 5
请问这个怎么搞定呀。
2011-05-20 11:00:00 2
2011-05-20 11:01:00 3
2011-05-20 11:02:00 4
2011-05-20 11:03:00 5
select count(*) from t where c_Date<=to_date(20110520110000) 2
select count(*) from t where c_Date<=to_date(20110520110100) 3
select count(*) from t where c_Date<=to_date(20110520110200) 4
select count(*) from t where c_Date<=to_date(20110520110300) 5
union all
select to_date('20110520110100','yyyy-mm-dd hh24:mi:ss'),count(*) form t where c_Date<=to_date(20110520110100)
union all
select to_date('20110520110200','yyyy-mm-dd hh24:mi:ss'),count(*) form t where c_Date<=to_date(20110520110200)
union all
select to_date('20110520110300','yyyy-mm-dd hh24:mi:ss'),count(*) form t where c_Date<=to_date(20110520110300))
select * from a;
2011-05-20 11:00:00 2
2011-05-20 11:00:00的时候数据库应该是2条数据2011-05-20 11:01:00 3
2011-05-20 11:01:00的时候数据库应该是3条数据2011-05-20 11:02:00 4
2011-05-20 11:02:00 的时候数据库应该是4数据2011-05-20 11:03:00 5
2011-05-20 11:03:00 的时候数据库应该是5数据其实就是三楼兄弟说的那种。但是我的是2年的数据 我不可能每一个都这么写、。
SQL> desc t
Name Type Nullable Default Comments
------ ------ -------- ------- --------
C_DATE DATE Y
ID NUMBER Y
SQL>
SQL> SELECT * FROM t;
C_DATE ID
----------- ----------
2011-5-20 1 1
2011-5-20 1 2
2011-5-20 1 3
2011-5-20 1 4
2011-5-20 1 5SQL> WITH tmp AS
2 (SELECT trunc(c_date, 'mi') c_date, id FROM t)
3 SELECT DISTINCT a.c_date,
4 (SELECT COUNT(*) FROM tmp b WHERE b.c_date <= a.c_date) cnt
5 FROM tmp a
6 ORDER BY c_date;
C_DATE CNT
----------- ----------
2011-5-20 1 2
2011-5-20 1 3
2011-5-20 1 4
2011-5-20 1 5
SQL>