PostgreSQL中SELECT DISTINCT结合自定义排序的报错解决咨询

表结构 #

ticket_entries表 #

id | ticket_status_id
1  | 2
2  | 3
3  | 4
4  | 3

operate_logs表 #

id | ticket_entry_id | action_type | createtime
 1 |     1           |   2         | 2022-10-13 15:25:25.202417+00
 2 |     1           |   3         | 2022-10-11 11:25:25.202417+00
 3 |     2           |   3         | 2022-10-12 12:25:25.202417+00
 4 |     3           |   1         | 2022-10-14 15:25:25.202417+00
 5 |     4           |   3         | 2022-10-15 15:25:25.202417+00

需求说明 #

需对ticket_status_id属于(3,4)的ticket_entry记录，按其关联的operate_log表中action_type为3的记录的createtime字段排序；ticket_status_id不在(3,4)或无action_type=3关联记录的ticket_entry需排在结果末尾。预期结果如下：

id | action_type
 4 |   3         
 2 |   3         
 1 |   2         
 3 |   4         

报错SQL及信息 #

尝试执行以下SQL时出现报错：

SELECT DISTINCT
    ( e.* ), o.create_time
FROM
    ticket_entries e
    LEFT JOIN operate_logs o ON e.ID = o.ticket_entry_id 
    AND o.action_type = 3 
ORDER BY
CASE
        WHEN e.ticket_status_id IN ( 3, 4 ) THEN
        1 
    END,
    o.create_time ASC NULLS LAST

报错信息：

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 10: CASE

可正常执行的SQL #

仅按o.create_time排序的SQL可正常执行：

SELECT DISTINCT
    (e.*), o.create_time
FROM
    ticket_entries e
    LEFT JOIN operate_logs o ON e.ID = o.ticket_entry_id 
    AND o.action_type = 3 
ORDER BY
    o.create_time ASC NULLS LAST

解决方案 #

已知ticket_entries与operate_logs为一对多关系，要让目标SQL正常执行，需要把ORDER BY中的CASE表达式加入到SELECT列表中——PostgreSQL要求SELECT DISTINCT语句里，ORDER BY的所有表达式必须出现在查询结果列中。

修改后的SQL（直接适配原写法） #

SELECT DISTINCT
    e.*, 
    o.create_time,
    CASE WHEN e.ticket_status_id IN (3, 4) THEN 1 END AS sort_priority
FROM
    ticket_entries e
    LEFT JOIN operate_logs o ON e.ID = o.ticket_entry_id 
    AND o.action_type = 3 
ORDER BY
    sort_priority ASC NULLS LAST,
    o.create_time ASC NULLS LAST

更高效的替代写法（避免DISTINCT开销） #

因为是一对多关联，用LEFT JOIN后会产生重复的ticket_entries记录，用子查询先获取每个ticket_entry对应action_type=3的createtime，再关联排序更高效：

SELECT 
    e.*,
    CASE WHEN e.ticket_status_id IN (3, 4) THEN 1 ELSE 2 END AS sort_priority,
    o.createtime AS sort_time
FROM
    ticket_entries e
LEFT JOIN (
    SELECT ticket_entry_id, createtime
    FROM operate_logs
    WHERE action_type = 3
) o ON e.id = o.ticket_entry_id
ORDER BY
    sort_priority ASC,
    sort_time ASC NULLS LAST

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