oracle 别名执行不了,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

(1)    FROM

(3)     JOIN

(2)    ON

(4)    WHERE

(5)    GROUP BY

(6)    HAVING

(9)    ORDER BY

(10)   LIMIT

(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 (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，hive中别名的使用都是严格遵循sql执行顺序的，groupby后面不能用别名。mysql特殊是因为mysql中对查询做了加强。

原文参考：https://blog..net/qq_26442553/article/details/80867076

原文参考：https://blog..net/crazyultimate/article/details/46778393