mysql - 如何从表中获取评论和评论回复

Question

我有一个评论表。

我想从表格中获取评论和回复，以这样的方式显示它们。我只是在寻找一个查询，这样我就可以做这样的事情

我做了什么我使用的查询

SELECT c.post_id, c.id AS comment_id, c.user_id, users.username, c.created, c.comment, r.id AS reply_id, r.parent_comment_id, r.created, r.comment AS reply, r.user_id AS reply_user_id, r_user.username as reply_username FROM (comments c) LEFT JOIN comments r ON c.id = r.parent_comment_id LEFT JOIN users ON c.user_id = users.id LEFT JOIN users as r_user ON r.user_id = r_user.id  WHERE r.id OR c.post_id IS NOT NULL ORDER BY parent_comment_id ASC;

Answer 1

也许是这样的:

SELECT
  c.comment,
  IFNULL(r.comment, '') reply
FROM 
  comments c
  LEFT JOIN comments r
    ON c.id = r.parent_comment_id 
WHERE 
  c.parent_comment_id is null