TSQL左连接，右上仅最后一行 [英] TSQL left join and only last row from right

问题描述

我正在编写SQL查询来获取帖子，并且仅发布该帖子的最后评论(如果存在). 但是我找不到一种方法来限制左联接中的右列仅限制一行.

I'm writing sql query to get post and only last comment of this post(if exists). But I can't find a way to limit only 1 row for right column in left join.

这是此查询的示例.

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

如果该帖子包含3条评论，则该帖子可获得3行，但我只希望有1条带有最后评论(按日期排序).

If post has 3 comments I get 3 rows with this post, but I want only 1 row with last comment(ordered by date).

有人可以帮我这个查询吗?

Can somebody help me with this query?

推荐答案

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

对于少数几个帖子，每个帖子都有很多评论，前者效率更高；对于许多帖子，每个帖子中都很少有评论，后者更为有效.

The former is more efficient for few posts with many comments in each; the latter is more efficient for many posts with few comments in each.

这篇关于TSQL左连接，右上仅最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！