在 PostgreSQL 中的 2 个不同表上加入 2 个选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16653828/
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
Joining 2 select queries on 2 different tables in PostgreSQL
提问by James
I have two separate queries on 2 different tables which I am trying to join into one query. Both tables are within the same schema.
我在 2 个不同的表上有两个单独的查询，我试图将它们加入一个查询中。两个表都在同一个架构中。
I am trying to form a query which will return the forumid, threadid and subject of the most recent post in a forum. I can use the two queries I wrote below, but for the sake of efficiency I would prefer to use just one if possible.
我正在尝试形成一个查询，该查询将返回论坛中最新帖子的 forumid、threadid 和主题。我可以使用我在下面写的两个查询，但为了效率起见，如果可能，我宁愿只使用一个。
The following are my queries:
以下是我的疑问：
1>
SELECT forumid,threadid
FROM threadtable
WHERE modifieddate = (select max(modifieddate) from threadtable);
2>
SELECT subject
FROM messsagetable
WHERE modifieddate = (select max(modifieddate) from messsagetable);
I have tried a few solutions but seem to be going round in circles. Any suggestions appreciated. Version is Postgres 8.1.
我尝试了一些解决方案，但似乎在兜圈子。任何建议表示赞赏。版本是 Postgres 8.1。
回答by Angelo Fuchs
SELECT * FROM
(SELECT forumid,threadid
FROM threadtable
WHERE modifieddate = (SELECT MAX(modifieddate) FROM threadtable)) a,
(SELECT subject
FROM messsagetable
WHERE modifieddate = (SELECT MAX(modifieddate) FROM messsagetable)) b
would combine all results from the first with all results from the second
将第一个的所有结果与第二个的所有结果结合起来
SELECT * FROM
(SELECT forumid,threadid, modifieddate
FROM threadtable
WHERE modifieddate = (SELECT MAX(modifieddate) FROM threadtable)) a
INNER JOIN
(SELECT subject, modifieddate
FROM messsagetable
WHERE modifieddate = (SELECT MAX(modifieddate) FROM messsagetable)) b
ON a.modifieddate = b.modifieddate
would combine all results from the first with all results from the second that have the same modifieddate.
会将第一个的所有结果与第二个具有相同修改日期的所有结果结合起来。
As both queries return only one resultrow, you most likely want the first suggestion.
由于两个查询只返回一个结果行，您很可能需要第一个建议。
回答by Erwin Brandstetter
To combine the results unconditionally use a CROSS JOIN:
要无条件地组合结果，请使用 a CROSS JOIN：
SELECT *
FROM (
SELECT forumid, threadid
FROM threadtable
ORDER BY modifieddate DESC
LIMIT 1
) t
CROSS JOIN (
SELECT subject
FROM messsagetable
ORDER BY modifieddate DESC
LIMIT 1
) m
I modified the base queries with the faster and simpler ORDER BY/ LIMIT 1. If there are multiple rows in either table sharing the maximum modifieddate, an arbitrary one will be picked.
You could add more items to the ORDER BYclause to pick a certain row in such a case.
我使用更快更简单的ORDER BY/修改了基本查询LIMIT 1。如果任一表中有多行共享最大值modifieddate，则将选择任意一个。在这种情况下，
您可以向ORDER BY子句添加更多项目以选择特定行。
Update after comment
评论后更新
however the modifieddate field is in both tables and in the case of the entries I am trying to retrieve it will be the same.
但是，修改日期字段在两个表中，并且在我尝试检索的条目的情况下，它将是相同的。
That's a broken design. You need a current version of Postgres and you need to reconsider your database layout.
As for now, the above query still does the job as requested - once you got your table names straight.
那是一个破碎的设计。您需要当前版本的 Postgres，并且需要重新考虑您的数据库布局。
至于现在，上面的查询仍然按照要求完成工作 - 一旦你得到了你的表名。
