postgresql 如何在 Postgres 中为联合查询自定义排序顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31023565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to have a custom sort order for a union query in Postgres
提问by Dave
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'
Or
或者
ORDER BY
CASE
WHEN name='East' THEN 1
WHEN name='West' THEN 2
WHEN name='Both' THEN 3
ELSE 4
END;
However, Postgres complains with:
但是，Postgres 抱怨：
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.
Do I have any alternative?
我有什么选择吗？
回答by a_horse_with_no_name
Wrap it in a derived table (which is what "HINT: .... or move the UNION into a FROM clause" is suggesting)
将其包装在派生表中（这就是“ HINT: .... or move the UNION into a FROM clause”的建议）
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;
回答by Craig Ringer
I'd add an extra column showing the desired ordering, then use ordinal column positions in the ORDER BY, e.g.
我会添加一个额外的列来显示所需的排序，然后在 中使用序数列位置ORDER BY，例如
SELECT 1, 'East' AS name, *
...
UNION ALL
SELECT 2, 'West' AS name, *
...
ORDER BY 1
Note that you probably also want UNION ALLsince your added columns ensure that every set in the union must be distinct anyway.
请注意，您可能还需要，UNION ALL因为您添加的列确保联合中的每个集合无论如何都必须是不同的。
回答by Pankus
By adding an extra column for ordering purpose, however it makes the UNION clause to work exactly as a UNION ALL (it does not eliminate duplicate rows from the result).
通过为排序目的添加额外的列，它使 UNION 子句完全像 UNION ALL 一样工作（它不会从结果中消除重复的行）。
