mysql - 帖子的 SQL 分页和每个帖子的 3 个最新评论

Question

我有一个 MySQL 查询，它为每个帖子选择最多 3 条评论(如果存在):

SELECT p.*, c.* 
FROM posts p 
LEFT JOIN comments c ON (p.post_id=c.post_id) 
AND c.comment_id > 
   (SELECT comment_id FROM comments 
    WHERE post_id=p.post_id ORDER BY comment_id DESC LIMIT 3
   )

我还想在帖子上引入分页。例如，我想限制每个页面只有 10 个帖子。

澄清一下 - 我的意思是 10 个不同的分页帖子，而不是结果查询中的 3 个帖子和每个帖子的 3 个评论。我的意思是这些帖子有 10 个不同的帖子 * 评论(每个帖子最多 3 个)。

如何在此查询中的帖子上引入分页？

另一种解决方案:

此外，我还为这项任务找到了另一种解决方案:

SELECT *
FROM
(
    SELECT *
    FROM posts
    LIMIT 0, 10
) p
LEFT JOIN
(
    SELECT c.*, 
        @rownumber := CASE WHEN @post_id = post_id THEN @rownumber + 1 ELSE 1 END AS n,
        @post_id := post_id
    FROM comments c,
    (SELECT @rownumber := 0, @post_id := 0) r    
    ORDER BY post_id DESC
) c ON p.post_id = c.post_id
WHERE c.post_id IS NULL OR n BETWEEN 1 and 3

Answer 1

基本答案

您在 SELECT 中为您的评论使用的相同语法:

LIMIT 0, 10

但是，您的查询使用 LIMIT 3, 1(仅返回第四行)而不是 LIMIT 0, 3 或 LIMIT 3 时出现错误。

扩展答案

这不是一个容易编写的查询，因为它非常复杂，但我在下面写了一个更简单的版本，可以满足您的需求。如果你想选择 30 条评论，每个帖子最多 3 条评论，这会容易得多，但这显然不是你想要做的。

基本上，查询返回前 10 个 posts，每个评论有 2 个字段(comments.id 和 comments.comment)，三个评论总共有 6 个字段。

它是如何做到这一点的，方法是对评论表使用三个 LEFT JOIN，每个 id 与之前的 LEFT JOIN 不同.这会为每个帖子创建最多三行，因此我们随后使用 GROUP BY p.id 将它们全部合并为一行。
该查询还使用 ORDER BY p.id ASC，因为使用 LIMIT 而不使用 ORDER BY 是不可预测的(数据库引擎在大多数情况下会在内部应用结果的 ORDER BY 子句)。

Note: If you have an INDEX on comments.post_id, this query will be extremely fast.

MySQL查询

SELECT p.*,
       c1.id AS comment1_id, c1.comment AS comment1,
       c2.id AS comment2_id, c2.comment AS comment2,
       c3.id AS comment3_id, c3.comment AS comment3

FROM posts AS p

LEFT JOIN comments AS c1 ON c1.post_id = p.id
LEFT JOIN comments AS c2 ON c2.post_id = p.id AND c2.id <> c1.id
LEFT JOIN comments AS c3 ON c3.post_id = p.id AND c3.id <> c1.id AND c3.id <> c2.id

GROUP BY p.id
ORDER BY p.id ASC
LIMIT 0, 10;

结果

| id |       post | comment1_id |  comment1 | comment2_id |  comment2 | comment3_id |  comment3 |
|----|------------|-------------|-----------|-------------|-----------|-------------|-----------|
|  1 |  Post 1... |           1 | Comment 1 |           2 | Comment 2 |      (null) |    (null) |
|  2 |  Post 2... |           3 | Comment 3 |           8 | Comment 8 |           9 | Comment 9 |
|  3 |  Post 3... |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |
|  4 |  Post 4... |           4 | Comment 4 |           5 | Comment 5 |           7 | Comment 7 |
|  5 |  Post 5... |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |
|  6 |  Post 6... |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |
|  7 |  Post 7... |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |
|  8 |  Post 8... |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |
|  9 |  Post 9... |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |
| 10 | Post 10... |           6 | Comment 6 |      (null) |    (null) |      (null) |    (null) |

See http://sqlfiddle.com/#!9/182db/4 for an online example.

通过检查字段是否为 null，您可以检查该帖子是否有评论，以及该帖子有多少评论。

Note: You should stick with UPPERCASE for the SQL keywords, since that is the accepted industry best practise.