从具有非唯一外键的表中选择N行

I have asked a similar question before and while the answers I got were spectacular I might need to clearify.

我之前已经问了一个类似的问题,虽然我得到的答案非常壮观,但我可能需要澄清。

Just like This question I want to return N number of rows depending on a value in a column.

就像这个问题我想根据列中的值返回N行。

My example will be I have a blog where I want to show my posts along with a preview of the comments. The last three comments to be exact.

我的例子是我有一个博客,我想要显示我的帖子以及评论的预览。最后三条评论确切。

I have have I need for my posts but I am racking my brain to get the comments right. The comments table has a foreign key of post_id which obviously multiple comments can be attached to one post so if a post has 20 comments then I just want to return the last three. What makes this somewhat tricky is I want to do it in one query and not a "limit 3" query per blog post which makes rendering a page with a lot of posts very query heavy.

我有我需要的帖子但我正在绞尽脑汁才能正确评论。注释表有一个post_id的外键,显然可以在一个帖子上附加多条评论,所以如果帖子有20条评论,那么我只想返回最后三条。是什么让这有点棘手的是我想在一个查询中做到这一点,而不是每个博客文章的“限制3”查询,这使得渲染一个页面的帖子很多,查询量很大。

SELECT *
FROM replies
GROUP BY post_id
HAVING COUNT( post_id ) <=3

This query does what I want but only returns one of each comment and not three.

此查询执行我想要的但只返回每个注释中的一个而不是三个。

3 个解决方案

#1

SELECT  l.*
FROM    (
        SELECT  post_id,
                COALESCE(
                (
                SELECT  id
                FROM    replies li
                WHERE   li.post_id = dlo.post_id
                ORDER BY
                        li.post_id, li.id
                LIMIT 2, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
        FROM    (
                SELECT  DISTINCT post_id
                FROM    replies dl
                ) dlo
        ) lo, replies l
WHERE   l.replies >= lo.replies
        AND l.replies <= lo.replies
        AND l.id <= lo.mid

Having an index on replies (post_id, id) (in this order) will greatly improve this query.

回复索引(post_id,id)(按此顺序)将大大改善此查询。

Note the usage of l.replies >= lo.replies AND l.replies <= lo.replies: this is to make the index to be usable.

注意l.replies> = lo.replies AND l.replies <= lo.replies的用法:这是为了使索引可用。

See the article in my blog for details:

有关详细信息,请参阅我博客中的文章:

Advanced row sampling (how to select N rows from a table for each GROUP)

高级行采样(如何从表中为每个GROUP选择N行)

#2

Do you track comment date? You can sort those results to grab only the 3 most recent ones.

你跟踪评论日期?您可以对这些结果进行排序,以便仅抓取最近的3个结果。

#3

following ian Jacobs idea

遵循伊恩雅各布斯的想法

declare @PostID int

声明@PostID int

select top 3 post_id, comment
from replies
where post_id=@PostID
order by createdate desc

#1

SELECT  l.*
FROM    (
        SELECT  post_id,
                COALESCE(
                (
                SELECT  id
                FROM    replies li
                WHERE   li.post_id = dlo.post_id
                ORDER BY
                        li.post_id, li.id
                LIMIT 2, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
        FROM    (
                SELECT  DISTINCT post_id
                FROM    replies dl
                ) dlo
        ) lo, replies l
WHERE   l.replies >= lo.replies
        AND l.replies <= lo.replies
        AND l.id <= lo.mid