最初由 binhu 发布
[B]目标:以每六小时为间隔,然后算出各自里的总登入次数
如idtime
1 2006-10-11 1:11:11
2 2006-10-11 2:13:10
3 2006-10-11 7:10:10
4 2006-10-11 9:12:12
我要结果是
0-62
6-12 2
12-18 0
18-24 0
请指点一下sql应该要怎样下,谢谢帮忙了 [/B]
SQL> select * from test;
RQ
-----------
2006-10-11
2006-10-11
2006-10-11
2006-10-11
SQL> select case when to_char(rq,'hh24') between '01' and '06' then '01-06' when to_char(rq,'hh24') between '07' and '17' then '07-12' when to_char(rq,'hh24') between '13' and '18' then '13-18' when to_char(rq,'hh24') between '19' and '24' then '19-24' end,count(*) from test
2group by case when to_char(rq,'hh24') between '01' and '06' then '01-06' when to_char(rq,'hh24') between '07' and '17' then '07-12' when to_char(rq,'hh24') between '13' and '18' then '13-18' when to_char(rq,'hh24') between '19' and '24' then '19-24' end;
CASEWHENTO_CHAR(RQ,'HH24')BETW COUNT(*)
------------------------------ ----------
01-06
2
07-12
2
|