如何在 Postgres 中为联合查询设置自定义排序顺序 [英] How to have a custom sort order for a union query in Postgres

问题描述

使用这样的查询(为了清楚起见而简化):

With a query like this (simplified for clarity):

SELECT 'East' AS name, *
FROM events 
WHERE event_timestamp BETWEEN '2015-06-14 06:15:00' AND '2015-06-21 06:15:00' 

UNION

SELECT 'West' AS name, *
FROM events 
WHERE event_timestamp BETWEEN '2015-06-14 06:15:00' AND '2015-06-21 06:15:00'

UNION

SELECT 'Both' AS name, *
FROM events 
WHERE event_timestamp BETWEEN '2015-06-14 06:15:00' AND '2015-06-21 06:15:00'

我想自定义结果行的顺序.类似的东西:

I want to customise the order of the resulting rows. Something like:

ORDER BY name='East', name='West', name='Both'

或

ORDER BY 
    CASE
        WHEN name='East' THEN 1 
        WHEN name='West' THEN 2
        WHEN name='Both' THEN 3
        ELSE 4
    END;

然而，Postgres 抱怨:

However, Postgres complains with:

ERROR:  invalid UNION/INTERSECT/EXCEPT ORDER BY clause
DETAIL:  Only result column names can be used, not expressions or functions.
HINT:  Add the expression/function to every SELECT, or move the UNION into a FROM clause.

我有其他选择吗?

推荐答案

将其包装在派生表中(这就是HINT: .... or move the UNION into a FROM clause"建议)

Wrap it in a derived table (which is what "HINT: .... or move the UNION into a FROM clause" is suggesting)

select *
from (
  ... your union goes here ... 
) t
order by
    CASE
        WHEN name='East' THEN 1 
        WHEN name='West' THEN 2
        WHEN name='Both' THEN 3
        ELSE 4
    END;

这篇关于如何在 Postgres 中为联合查询设置自定义排序顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！