在 PostgreSQL 中的 2 个不同表上加入 2 个选择查询 [英] Joining 2 select queries on 2 different tables in PostgreSQL

问题描述

我在 2 个不同的表上有两个单独的查询，我试图将它们加入一个查询中.两个表都在同一个架构中.

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.

我正在尝试形成一个查询，该查询将返回论坛中最新帖子的 forumid、threadid 和主题.我可以使用我在下面写的两个查询，但为了效率起见，如果可能，我宁愿只使用一个.

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.

以下是我的疑问:

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);

我尝试了一些解决方案，但似乎都在兜圈子.任何建议表示赞赏.版本为 Postgres 8.1.

I have tried a few solutions but seem to be going round in circles. Any suggestions appreciated. Version is Postgres 8.1.

推荐答案

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.

这篇关于在 PostgreSQL 中的 2 个不同表上加入 2 个选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！