SQL 按時間段來查詢
如表1:
2007-6-27 10:00 20
2007-6-27 11:00 60
2007-6-27 11:25 15
2007-6-27 12:05 100
2007-6-27 12:59 10
2007-6-27 13:00 150
出來的效果是要
2007-6-27 10:00-10:59 20
2007-6-27 11:00-11:59 75
2007-6-27 12:00-12:59 110
2007-6-27 13:00-13:59 150
我想要這樣的效果....怎樣做呢???謝謝
[解决办法]
在oracle中可以这样实现
create table t(cdate date,cnum int);
/
insert into t
select to_date( '2007-6-27 10:00 ', 'yyyy-mm-dd HH24:MI '),20 from dual union all
select to_date( '2007-6-27 11:00 ', 'yyyy-mm-dd HH24:MI '),60 from dual union all
select to_date( '2007-6-27 11:25 ', 'yyyy-mm-dd HH24:MI '),15 from dual union all
select to_date( '2007-6-27 12:05 ', 'yyyy-mm-dd HH24:MI '),100 from dual union all
select to_date( '2007-6-27 12:59 ', 'yyyy-mm-dd HH24:MI '),10 from dual union all
select to_date( '2007-6-27 13:00 ', 'yyyy-mm-dd HH24:MI '),150 from dual;
/
select to_char( cdate, 'yyyy-mm-dd ' )|| ' '||to_char( cdate, 'HH24 ' )|| ':00- '||to_char( cdate, 'HH24 ' )|| ':59 ', sum(cnum) from t group by to_char( cdate, 'yyyy-mm-dd ' ),to_char( cdate, 'HH24 ' )
--执行结果
2007-06-27 10:00-10:5920
2007-06-27 11:00-11:5975
2007-06-27 12:00-12:59110
2007-06-27 13:00-13:59150
