Oracle 等中的 group by与where 子句不能使用别名的原因与解决办法

下面的语句执行的话会报错：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语句执行顺序为:

(7)    SELECT

(8)    DISTINCT <select_list>

(1)    FROM <left_table>

(3)    <join_type> JOIN <right_table>

(2)    ON <join_condition>

(4)    WHERE <where_condition>

(5)    GROUP BY <group_by_list>

(6)    HAVING <having_condition>

(9)    ORDER BY <order_by_condition>

(10)   LIMIT <limit_number> 

（7）之后，比如order中，distinct中。

这是因为在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;

注意：
           在mysql中，group by中可以使用别名；where中不能使用别名；order by中可以使用别名。其余像oracle，hiving中别名的使用都是严格遵循sql执行顺序的，groupby后面不能用别名。mysql特殊是因为mysql中对查询做了加强。

原文参考：https://blog.youkuaiyun.com/qq_26442553/article/details/80867076 
原文参考：https://blog.youkuaiyun.com/crazyultimate/article/details/46778393