SQL TSQL 左连接并且仅从右起最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2281551/
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
TSQL left join and only last row from right
提问by barbarian
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.
我正在编写 sql 查询以获取帖子，并且仅是该帖子的最后一条评论（如果存在）。但是我找不到一种方法来限制左连接中右列的仅 1 行。
Here is sample of this query.
这是此查询的示例。
SELECT post.id, post.title,comment.id,comment.message
from post
left outer join comment
on post.id=comment.post_id
If post has 3 comments I get 3 rows with this post, but I want only 1 row with last comment(ordered by date).
如果帖子有 3 条评论，我会在这篇文章中得到 3 行，但我只需要 1 行最后一条评论（按日期排序）。
Can somebody help me with this query?
有人可以帮我解决这个查询吗？
回答by Quassnoi
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
or
或者
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.
前者对于每条评论多的少数帖子更有效；后者对于许多帖子更有效，每个帖子几乎没有评论。
回答by AGoodDisplayName
Subquery:
子查询：
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(c.id) FROM comment c2 WHERE c2.post_id = p.id)
回答by Paul Williams
You'll want to join to a sub-query that returns the last comment for the post. For example:
您需要加入一个返回帖子最后评论的子查询。例如：
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
回答by Ian Jacobs
Couple of options....
几个选项....
One way is to do the JOIN on:
一种方法是在以下位置执行 JOIN：
SELECT TOP 1 comment.message FROM comment ORDER BY comment.id DESC
(note I'm assuming that comment.id is an Identity field)
（注意我假设 comment.id 是一个 Identity 字段）
回答by No Refunds No Returns
what version of SQL Server? If you have the Row_Number() function available you can sort your comments by whatever "first" means to you and then just add a "where RN=1" clause. Don't have a handy example or the right syntax off the top of my head but do have tons of queries that do exactly this. Other posts are all in the 1,000's of ways you could do this.
什么版本的 SQL Server？如果您有可用的 Row_Number() 函数，您可以根据“第一”对您的意义对您的评论进行排序，然后添加一个“where RN=1”子句。没有一个方便的例子或正确的语法，但确实有大量的查询可以做到这一点。其他帖子都有 1,000 多种方法可以做到这一点。
I'd say profile it and see which one performs best for you.
我会说配置文件，看看哪一个最适合你。
回答by Andy Raddatz
You didn't say the specific name of your date field, so I filled in with [DateCreated]. This is essentially the same as AGoodDisplayName's post above, but using the date field instead of relying on the ID column ordering.
你没有说你的日期字段的具体名称，所以我用[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
)
