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

Question

我问了一个similar question之前和虽然我得到的答案非常壮观，但我可能需要澄清一下。

就像This question我想根据列中的值返回 N 行数。

我的例子是我有一个博客，我想在其中显示我的帖子以及评论的预览。确切地说是最后三个评论。

我有我需要的帖子，但我正在绞尽脑汁让评论正确。 comments 表有一个 post_id 外键，显然多个评论可以附加到一个帖子，所以如果一个帖子有 20 条评论，那么我只想返回最后三个。让这有点棘手的是我想在一个查询中完成它，而不是每个博客帖子的“限制 3”查询，这使得呈现包含大量帖子的页面非常查询。

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

这个查询做了我想要的，但只返回每个评论中的一个，而不是三个。

Answer 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

在 replies (post_id, id) 上有一个索引(按此顺序)将大大改进此查询。

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

详见我博客中的文章:

• Advanced row sampling (如何从表中为每个 N 选择 GROUP 行)