需要将原始语句改成列传行,原始语句如下:
写了一个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;