SELECT CASE WHEN 0<=tm AND tm<1 THEN '0-1' WHEN 1<=tm AND tm<2 THEN '1-2' WHEN 2<=tm AND tm<3 THEN '2-3' WHEN 3<=tm AND tm<4 THEN '3-4' WHEN 4<=tm AND tm<5 THEN '4-5' WHEN 5<=tm AND tm<6 THEN '5-6' WHEN 6<=tm AND tm<7 THEN '6-7' WHEN 7<=tm AND tm<8 THEN '7-8' WHEN 8<=tm AND tm<9 THEN '8-9' WHEN 9<=tm AND tm<10 THEN '9-10' WHEN 10<=tm AND tm<11 THEN '10-11' WHEN 11<=tm AND tm<12 THEN '11-12' WHEN 12<=tm AND tm<13 THEN '12-13' WHEN 13<=tm AND tm<14 THEN '13-14' WHEN 14<=tm AND tm<15 THEN '14-15' WHEN 15<=tm AND tm<16 THEN '15-16' WHEN 16<=tm AND tm<17 THEN '16-17' WHEN 17<=tm AND tm<18 THEN '17-18' WHEN 18<=tm AND tm<19 THEN '18-19' WHEN 19<=tm AND tm<20 THEN '19-20' WHEN 20<=tm AND tm<21 THEN '20-21' WHEN 21<=tm AND tm<22 THEN '21-22' WHEN 22<=tm AND tm<23 THEN '22-23' ELSE '23-24' END 时间 , 版本一,版本二,版本三,版本四,总计 FROM ( SELECT To_Number(To_Char(login_time,'hh24')) tm, Sum(decode(client_version_code,'000000000',login_count)) 版本一, Sum(decode(client_version_code,'000000001',login_count)) 版本二, Sum(decode(client_version_code,'000000002',login_count)) 版本三, Sum(decode(client_version_code,'000000003',login_count)) 版本四, Sum(login_count) 总计 from t GROUP BY To_Char(login_time,'hh24') 之前有人发的,给你参考下,忘了在哪了
不嫌麻烦的话自己写个行转列的sql也可以
select count(*) ,trunc(rec_date,'hh24') from a where v_date>=trunc(to_date('20091001','yyyymmdd')) and v_date<=trunc(to_date('20091101','yyyymmdd')) group by trunc(rec_date,'hh24') 这是我的查询语句
CASE WHEN 0<=tm AND tm<1 THEN '0-1'
WHEN 1<=tm AND tm<2 THEN '1-2'
WHEN 2<=tm AND tm<3 THEN '2-3'
WHEN 3<=tm AND tm<4 THEN '3-4'
WHEN 4<=tm AND tm<5 THEN '4-5'
WHEN 5<=tm AND tm<6 THEN '5-6'
WHEN 6<=tm AND tm<7 THEN '6-7'
WHEN 7<=tm AND tm<8 THEN '7-8'
WHEN 8<=tm AND tm<9 THEN '8-9'
WHEN 9<=tm AND tm<10 THEN '9-10'
WHEN 10<=tm AND tm<11 THEN '10-11'
WHEN 11<=tm AND tm<12 THEN '11-12'
WHEN 12<=tm AND tm<13 THEN '12-13'
WHEN 13<=tm AND tm<14 THEN '13-14'
WHEN 14<=tm AND tm<15 THEN '14-15'
WHEN 15<=tm AND tm<16 THEN '15-16'
WHEN 16<=tm AND tm<17 THEN '16-17'
WHEN 17<=tm AND tm<18 THEN '17-18'
WHEN 18<=tm AND tm<19 THEN '18-19'
WHEN 19<=tm AND tm<20 THEN '19-20'
WHEN 20<=tm AND tm<21 THEN '20-21'
WHEN 21<=tm AND tm<22 THEN '21-22'
WHEN 22<=tm AND tm<23 THEN '22-23'
ELSE '23-24' END 时间 ,
版本一,版本二,版本三,版本四,总计
FROM (
SELECT
To_Number(To_Char(login_time,'hh24')) tm,
Sum(decode(client_version_code,'000000000',login_count)) 版本一,
Sum(decode(client_version_code,'000000001',login_count)) 版本二,
Sum(decode(client_version_code,'000000002',login_count)) 版本三,
Sum(decode(client_version_code,'000000003',login_count)) 版本四,
Sum(login_count) 总计
from t
GROUP BY To_Char(login_time,'hh24')
之前有人发的,给你参考下,忘了在哪了
from a
where v_date>=trunc(to_date('20091001','yyyymmdd'))
and v_date<=trunc(to_date('20091101','yyyymmdd'))
group by trunc(rec_date,'hh24')
这是我的查询语句