PostgreSQL：如何合并按日期排序的 3 个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4232626/
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
PostgreSQL: How to union 3 tables sorted by date
提问by GabiMe
I have 3 different SQL queries from 3 different unrelated tables (all using LIMIT and ORDER BY).
我有来自 3 个不同的不相关表的 3 个不同的 SQL 查询（都使用 LIMIT 和 ORDER BY）。
I would like to merge and sort the results according to the "date" field (which appears in all of them)
我想根据“日期”字段（出现在所有这些字段中）对结果进行合并和排序
What is the SQL to do this?
执行此操作的 SQL 是什么？
回答by Mark Byers
The best way is to create a new table containing the common fields from the three other tables and add an index on the common date field. The original three tables should contain a foreign key linking to the common table. With this design the query becomes simple:
最好的方法是创建一个包含来自其他三个表的公共字段的新表，并在公共日期字段上添加索引。原来的三个表应该包含一个链接到公共表的外键。通过这种设计，查询变得简单：
SELECT *
FROM common_table
ORDER BY "date" DESC
LIMIT 100
If you also need data from the more specific tables you can use LEFT JOINs to also select that data in the same query.
如果您还需要更具体的表中的数据，您可以使用 LEFT JOIN 在同一查询中也选择该数据。
If you can't change your design and performance is not an issue then you can use UNION ALL to combine the results from all three tables before sorting:
如果你不能改变你的设计并且性能不是问题，那么你可以在排序之前使用 UNION ALL 组合来自所有三个表的结果：
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
UNION ALL
SELECT * FROM table3
ORDER BY "date" DESC
LIMIT 100
Note that the above will only work if all tables have the same structure. If you have fields that occur in one table but not in others then you should omit them from the SELECT or else return NULL for that column in the other tables. For example if:
请注意，以上仅在所有表具有相同结构时才有效。如果您的字段出现在一个表中但不在其他表中，那么您应该从 SELECT 中省略它们，否则为其他表中的该列返回 NULL。例如，如果：
table1has columnsa,b,canddate.table2has columnsb,canddate.table3has columnsa,canddate.
table1有列a，b，c和date。table2有列b，c和date。table3有列a，c和date。
Then use this:
然后使用这个：
SELECT a, b, c, "date"
FROM table1
UNION ALL
SELECT NULL AS a, b, c, "date"
FROM table2
UNION ALL
SELECT a, NULL as b, c, "date"
FROM table3
ORDER BY "date" DESC
LIMIT 100
回答by Marco Mariani
SELECT.....
UNION ALL
SELECT....
UNION ALL
SELECT ...
ORDER BY date_field;
For the best performance, apply ORDER BY / LIMIT as late as possible, and avoid it in subqueries.
为了获得最佳性能，尽可能晚地应用 ORDER BY / LIMIT，并避免在子查询中使用。
