Oracle pl/sql中的group by子句不能使用别名，应该这样写。

下面的语句执行的话会报错：ORA-00904: "CALLT": 标示符无效

select case when ta.call_time = 0 then 0
    when ta.call_time <= 6 and ta.call_time > 0 then 1
    when ta.call_time <= 60 and ta.call_time > 6 then 2
    when ta.call_time <= 3600 and ta.call_time > 60 then 3
    else 4 end as callt,
      count(ta.annoyanceid) as counts 
from t_annoyance ta
group by callt;

这是因为在SQL执行的时候，WHERE和GROUP语句在字段分类之前就已经执行了，在此期间，别名还没有生效，因此找不到指定别名的字段，报错。

除了聚合函数之外的表达式不仅仅是一个字段，所以用别名代替，这种情况怎么写呢？有三种方法。

方法一，只写表达式中存在的字段

select case when ta.call_time = 0 then 0
    when ta.call_time <= 6 and ta.call_time > 0 then 1
    when ta.call_time <= 60 and ta.call_time > 6 then 2
    when ta.call_time <= 3600 and ta.call_time > 60 then 3
    else 4 end as callt,
      count(ta.annoyanceid) as counts 
from t_annoyance ta
group by ta.call_time;

方法二，将表达式都写入group by子句

select case when ta.call_time = 0 then 0
    when ta.call_time <= 6 and ta.call_time > 0 then 1
    when ta.call_time <= 60 and ta.call_time > 6 then 2
    when ta.call_time <= 3600 and ta.call_time > 60 then 3
    else 4 end as callt,
      count(ta.annoyanceid) as counts 
from t_annoyance ta
group by case when ta.call_time = 0 then 0
    when ta.call_time <= 6 and ta.call_time > 0 then 1
    when ta.call_time <= 60 and ta.call_time > 6 then 2
    when ta.call_time <= 3600 and ta.call_time > 60 then 3
    else 4 end;

方法三，将表达式放入子查询

select t1.callt,
      count(t2.annoyanceid) as counts 
from (<strong>select case when ta.call_time = 0 then 0
    when ta.call_time <= 6 and ta.call_time > 0 then 1
    when ta.call_time <= 60 and ta.call_time > 6 then 2
    when ta.call_time <= 3600 and ta.call_time > 60 then 3
    else 4 end as callt
      , ta.annoyanceid 
      from t_annoyance ta) t1
      , t_annoyance t2
where t1.annoyanceid = t2.annoyanceid
group by t1.callt;