mysql left join with limit 0,2

Question

评论表

+------+----------+
| id   | comment  | 
+------+----------+
|    1 | foo      |
|    2 | bar      |
|    3 | foobar   |
+------+----------+

回复表

+------+----------+------------+
| id   | reply   |comment_id  |
+------+----------+------------+
|    1 | nice lol | 1          |
|    2 | ok ok    | 2          |
|    3 | hello    | 1          |
|    4 | hello2   | 1          |
|    5 | hello1   | 1          |
+------+----------+------------+



 SELECT
`comment`.`comment`,
`x`.`reply`
FROM `comment` LEFT JOIN
(SELECT GROUP_CONCAT(`reply`) as reply ,reply.commnt_id FROM `reply` 
GROUP BY   `reply`.`comment_id` ORDER BY `reply`.`id` LIMIT 0,1)x ON x.comment_id =   comment.id

结果会是

+----------+-----------------+  
| comment  | reply          |  
+----------+-----------------+ 
|    foo   | nice lol,hello  | 
|    bar   | NULL            | 
|    off   | null            | 
+------+---------------------+ 

为什么第二条评论为空，但如果我将限制设置为 0,4，它会显示它的问题

Answer 1

Mysql 不支持group by 子句中的限制。为了实现这种类型的功能，我们可以破解 group_concat，如下所示:

SELECT
comment.comment,
x.replay
FROM comment LEFT JOIN
 (SELECT 
    REPLACE(substring_index(group_concat(replay SEPARATOR '@@'), '@@', 2), '@@', ',') as replay ,replay.commnt_id 
  FROM replay 
  GROUP BY  replay.comment_id ORDER BY replay.id LIMIT 0,1)x 
ON x.comment_id =   comment.id

这是考虑到您的回复中不会包含“@@”。

相关帖子:

GROUP_CONCAT with limit

Mysql group_concat limit rows in grouping