![](http://img.blog.itpub.net/blog/attachment/201508/27/26506993_1440642955NPZG.png?x-oss-process=style/bb)
写了一个SQL,语句是这样的
select to_char('countdate', 'YYYY-MM-DD') "日期",
count(case
when activetime > 60 * 60 then
count(1)
end) ">=1h",
count(case
when activetime > 120 * 60 then
count(1)
end) ">=2h",
count(case
when activetime > 180 * 60 then
count(1)
end) ">=3h",
count(case
when activetime > 240 * 60 then
count(1)
end) ">=4h",
count(case
when activetime > 300 * 60 then
count(1)
end) ">=5h",
count(case
when activetime > 360 * 60 then
count(1)
end) ">=6h"
from ELMP_ANALYSIS_LOGINTIMEDETAIL
where activetime > 60 * 60
and countdate between to_date('&1', 'YYYY-MM-DD') and
to_date('&2', 'YYYY-MM-DD')
group by to_char('countdate', 'YYYY-MM-DD')
order by 1;
执行的时候, 一直报错 ORA-00937,仔细检查,发现在count里面还套了一层count,导致报错,修改后的语句如下:
select countdate "日期" count(case
when activetime > 60 * 60 then
activetime
end) ">=1h",
count(case
when activetime > 120 * 60 then
activetime
end) ">=2h",
count(case
when activetime > 180 * 60 then
activetime
end) ">=3h",
count(case
when activetime > 240 * 60 then
activetime
end) ">=4h",
count(case
when activetime > 300 * 60 then
activetime
end) ">=5h",
count(case
when activetime > 360 * 60 then
activetime
end) ">=6h"
from ELMP_ANALYSIS_LOGINTIMEDETAIL
where activetime > 60 * 60
and countdate between to_date('&1', 'YYYY-MM-DD') and
to_date('&2', 'YYYY-MM-DD')
group by countdate
order by 1;来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-1783791/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26506993/viewspace-1783791/