mysql - JOIN中特定列的ORDER/LIMIT

This question already has answers here:

Using LIMIT within GROUP BY to get N results per group?

                                    （14个回答）


                                5年前关闭。


我有以下sql语句：

SELECT e.comment_id AS parentcomment,
m.comment_id AS child_id
FROM comments e
INNER JOIN comments m
ON e.comment_id=m.parent_id
WHERE e.parent='1' AND e.parent_id='$parentid' AND m.parent='0';

样本输出：

+---------------+----------+
| parentcomment | child_id |
+---------------+----------+
|             1 |        3 |
|             1 |        4 |
|             1 |        7 |
|             5 |        8 |
|             1 |        9 |
|             1 |       10 |
|             1 |       11 |
|             1 |       12 |
|             1 |       13 |
|             1 |       14 |
|             1 |       15 |
|             1 |       16 |
|             1 |       17 |
|             1 |       18 |
|             1 |       19 |
|             1 |       20 |
|             1 |       21 |
|             1 |       22 |
|             1 |       23 |
|             1 |       24 |
|             1 |       25 |
|            26 |       32 |
|            26 |       33 |
|            27 |       34 |
|            27 |       35 |
|            28 |       36 |
|            29 |       37 |
|            30 |       38 |
|            31 |       39 |
|            26 |       40 |
+---------------+----------+
30 rows in set

我想做的是-我只想为每个child_id值显示15个parentcomment值。除此之外，我只希望检索30个parentcomment值。

更重要的是，我想按某个索引（假设ORDER）parentcomment id值并仅选择前30个，然后从有序child_id值（也有序）中选择前15个通过id）。

希望这是有道理的。我该怎么做？

提前致谢。

最佳答案

您应该可以使用此（sqlFiddle）在这里，我只抓取3个（而不是15个）child_id和6个（而不是30个）parentcomment

在我的示例中，$parentid是1。最里面的查询是您刚添加ORDER BY parentcomment,child_id的原始查询

SELECT parentcomment,child_id,parentRank,childRank
FROM
    (SELECT parentcomment,
           child_id,
           IF (@prevparent <> parentcomment, @parentRank:=@parentRank+1, @parentRank) as parentRank,
           IF (@prevparent <> parentcomment, @childRank:=1, @childRank:=@childRank+1) as childRank,
           @prevparent := parentcomment
     FROM
       (SELECT e.comment_id AS parentcomment,
               m.comment_id AS child_id
        FROM comments e
        INNER JOIN comments m
        ON e.comment_id=m.parent_id
        WHERE e.parent='1' AND e.parent_id='1' AND m.parent='0'

          ORDER BY parentcomment,child_id
       )T1,
       (SELECT @prevparent:=0,@parentRank:=0,@childRank:=0)rank
     )T2
WHERE parentRank BETWEEN 1 AND 6
AND   childRank BETWEEN 1 AND 3

关于mysql - JOIN中特定列的ORDER/LIMIT ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20767317/

ParentComment

mysql - JOIN中特定列的ORDER/LIMIT