下面的语句执行的话会报错: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.csdn.net/qq_26442553/article/details/80867076
原文参考:https://blog.csdn.net/crazyultimate/article/details/46778393