Oracle SELECT自定义排序报错ORA-01785问题咨询

错误原因 #

在使用UNION这类集合操作时，Oracle对ORDER BY子句有严格限制：只能引用SELECT列表中表达式的序号，或者将排序逻辑包含在主查询的SELECT列表内。直接在ORDER BY中使用DECODE这类复杂函数，会触发ORA-01785错误。

解决方案 #

方法1：将排序表达式加入主查询SELECT列表 #

把排序用的DECODE逻辑作为一个字段包含在查询中，通过别名或序号排序：

select t.status, t.cnt
from (
    select status, count(*) as cnt, decode(status, 'SENT', 1, 'ACK', 2, 'NACK', 3) as sort_order
    from (
        select status, count(*) from test1 
        where trunc(update_ts) = trunc(sysdate) and status in ('ACK','NACK') 
        group by status
        union
        select 'SENT' as status, count(*) from test1 
        where trunc(update_ts) = trunc(sysdate)
    ) t
)
order by sort_order;

方法2：用SELECT列表序号指定排序 #

原查询的SELECT列表只有两列（status是第1列，count(*)是第2列），可以直接引用列序号来执行排序逻辑：

select * 
from (
    select status, count(*) from test1 
    where trunc(update_ts) = trunc(sysdate) and status in ('ACK','NACK') 
    group by status
    union
    select 'SENT' as status, count(*) from test1 
    where trunc(update_ts) = trunc(sysdate)
) 
order by decode(1, 'SENT', 1, 'ACK', 2, 'NACK', 3); -- 1对应SELECT列表的第1列status

更优写法（推荐） #

合并子查询，避免UNION带来的限制，同时减少表扫描次数：

select 
    case when status in ('ACK','NACK') then status else 'SENT' end as status,
    count(*)
from test1 
where trunc(update_ts) = trunc(sysdate)
group by case when status in ('ACK','NACK') then status else 'SENT' end
order by decode(status, 'SENT', 1, 'ACK', 2, 'NACK', 3);

内容的提问来源于stack exchange，提问作者upog