TSQL左连接和只选右表最后一行

Question

我正在编写SQL查询来获取帖子并仅获取该帖子的最后一条评论（如果存在）。 但是我找不到一种方法来限制左连接中右侧列的行数仅为1。

以下是此查询的示例。

SELECT post.id, post.title,comment.id,comment.message
from post
left outer join comment
on post.id=comment.post_id

如果一篇文章有3条评论，我会得到与此文章对应的3行数据，但是我只想要最后一条评论的数据（按日期排序），并且只需要一行。

有谁可以帮助我编写这个查询吗？

- barbarian

Answer 1

SELECT  post.id, post.title, comment.id, comment.message
FROM    post
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    comment с
        WHERE   c.post_id = post.id
        ORDER BY
                date DESC
        ) comment

或者

SELECT  *
FROM    (
        SELECT  post.id, post.title, comment.id, comment.message,
                ROW_NUMBER() OVER (PARTITION BY post.id ORDER BY comment.date DESC) AS rn
        FROM    post
        LEFT JOIN
                comment
        ON      comment.post_id = post.id
        ) q
WHERE   rn = 1

如果每个帖子都有很多评论，则前一种方法对于评论较少的帖子更有效；如果每个帖子只有几条评论，则后一种方法对于帖子很多的情况更有效。

- Quassnoi

---

谢谢回答。我使用下面的代码。选择 post.id、post.title、c.id 作为 comment_id，c.message 从 post 左外连接（select comment.id、comment.post_id、comment.message、 ROW_NUMBER() OVER (PARTITION BY comment.post_id ORDER BY comment.date DESC) AS rn from comment）c on post.id=c.post_id where c.rn=1 或 c.rn 是空的。 - barbarian

APPLY运算符对我很有帮助。我正在进行一对多的连接，但需要将右侧匹配项减少到仅最近创建的项目。谢谢！ - Aaron Bennett

将这两个内容结合起来展示预估查询计划是非常有趣的，其中“apply”选项占据了99%的查询成本:o - Felipe Sabino

@FelipeSabino：如果你在大象笼子上看到一个“水牛”标志，请不要相信你的眼睛。 - Quassnoi

Answer 2

子查询：

SELECT p.id, p.title, c.id, c.message
FROM post p
LEFT join comment c
ON c.post_id = p.id AND c.id = 
                 (SELECT MAX(c2.id) FROM comment c2 WHERE c2.post_id = p.id)

- AGoodDisplayName

---

如果我没有漏掉什么，根据我的执行计划，这个程序应该比被接受的答案快得多（大约快10倍）。 - nikib3ro

对我来说，这比接受的答案中使用的笨重子查询更有效率。在https://dev59.com/p2445IYBdhLWcg3w0NOu中有一个相关的答案。它使用TOP 1 / ORDER BY解决方案而不是子查询中的MAX。 - Christiaan Westerbeek

我相信这是更短更好的答案。 - MurifoX

这将按ID排序返回最后一条评论，而不是OP请求的按日期排序。 - Piro

Answer 3

您需要加入到一个子查询中，该子查询返回该帖子的最后一条评论。例如：

select post.id, post.title. lastpostid, lastcommentmessage
from post
inner join
(
    select post.id as lastpostid, max(comment.id) as lastcommentmessage
    from post
    inner join comment on commment.post_id = post.id
    group by post.id
) lastcomment
    on lastpostid = post.id

- Paul Williams

Answer 4

你没有提到日期字段的具体名称，所以我用 [DateCreated] 填充了它。这与 AGoodDisplayName 在上面的帖子中基本相同，但使用日期字段而不是依赖 ID 列排序。

SELECT post.id, post.title, comment.id, comment.message
FROM post p
LEFT OUTER JOIN comment
ON comment.id = (
    SELECT TOP 1 id
    FROM comment
    WHERE p.id = post_id
    ORDER BY [DateCreated] ASC
)

- Andy Raddatz

Answer 5

有几个选项...

其中一种方法是在以下内容上执行JOIN：

SELECT TOP 1 comment.message FROM comment ORDER BY comment.id DESC

（请注意，我假设 comment.id 是标识字段）

- Ian Jacobs

---

如果身份字段具有负增量会怎样？ - No Refunds No Returns

你曾经遇到过这样的事情吗？ - Phil Factor

Answer 6

使用哪个版本的SQL Server？如果您可以使用Row_Number()函数，您可以按照您认为的“第一”方式对评论进行排序，然后只需添加一个“where RN=1”子句。我脑海中没有方便的示例或正确的语法，但确实有大量执行此操作的查询。其他帖子都是在1,000种方法中完成此操作。

我建议您对其进行分析，并查看哪个表现最佳。

- No Refunds No Returns