PostgreSQL：如何合并按日期排序的3个表 [英] PostgreSQL: How to union 3 tables sorted by date
问题描述
我从3个不同的不相关表（均使用LIMIT和ORDER BY）中进行了3个不同的SQL查询。
I have 3 different SQL queries from 3 different unrelated tables (all using LIMIT and ORDER BY).
我想根据日期字段（出现在所有字段中）
I would like to merge and sort the results according to the "date" field (which appears in all of them)
执行此操作的SQL是什么？
What is the SQL to do this?
推荐答案
最好的方法是创建一个包含其他三个表的公共字段的新表，并在公共日期字段上添加索引。原始的三个表应包含一个链接到公用表的外键。通过这种设计，查询变得非常简单：
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
如果您还需要更具体的表中的数据，则可以使用LEFT JOINs
If you also need data from the more specific tables you can use LEFT JOINs to also select that data in the same query.
如果无法更改设计，而性能也不是问题，则可以使用UNION ALL合并来自排序前的所有三个表：
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:
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
UNION ALL
SELECT * FROM table3
ORDER BY "date" DESC
LIMIT 100
请注意，以上内容仅在所有表都具有相同结构的情况下起作用。如果某个字段出现在一个表中，而其他字段中没有，则应从SELECT中忽略它们，否则在其他表中为该列返回NULL。例如，如果：
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:
-
table1具有列a，b，c和日期。 -
table2具有列b，c和date。 -
table3有列a，c和date。
table1has columnsa,b,canddate.table2has columnsb,canddate.table3has columnsa,canddate.
然后使用此：
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
这篇关于PostgreSQL：如何合并按日期排序的3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
