orcale中case when和group by同时使用会报无效标识符

不可以使用case when 的列名进行group by，如：

select count(1)/7 as "平均违法数量",
to_char(wfsj, 'yyyy-MM-dd' ) as time,
case
when (to_char(wfsj, 'hh24')) between 00 and 04 then
'00-04时'
when (to_char(wfsj, 'hh24')) between 04 and 08 then
'04-08时'
when (to_char(wfsj, 'hh24')) between 08 and 12 then
'08-12时'
when (to_char(wfsj, 'hh24')) between 12 and 16 then
'12-16时'
when (to_char(wfsj, 'hh24')) between 16 and 20 then
'16-20时'
when (to_char(wfsj, 'hh24')) between 20 and 24 then
'20-24时'
end as "违法时间"
from
vio_violation
where wfsj >= trunc(sysdate - 7)
group by "违法时间"；

正确sql：

select count(1)/7 as "平均违法数量",
to_char(wfsj, 'yyyy-MM-dd' ) as time,
case
when (to_char(wfsj, 'hh24')) between 00 and 04 then
'00-04时'
when (to_char(wfsj, 'hh24')) between 04 and 08 then
'04-08时'
when (to_char(wfsj, 'hh24')) between 08 and 12 then
'08-12时'
when (to_char(wfsj, 'hh24')) between 12 and 16 then
'12-16时'
when (to_char(wfsj, 'hh24')) between 16 and 20 then
'16-20时'
when (to_char(wfsj, 'hh24')) between 20 and 24 then
'20-24时'
end as "违法时间"
from
vio_violation
where wfsj >= trunc(sysdate - 7)
group by wfsj；

转载于:https://www.cnblogs.com/ww5566/p/11124851.html