关闭

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

2699人阅读 评论(0) 收藏 举报
分类:


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



1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:25030次
    • 积分:533
    • 等级:
    • 排名:千里之外
    • 原创:28篇
    • 转载:4篇
    • 译文:0篇
    • 评论:3条
    最新评论