MySql Join条件与Where条件获取数据列表

Question

我创建了以下表格 -

评论:

|  column   |    type     |
+-----------+-------------+
| comment_id| int(11)     |
| comment   | longText    |
| parent_id | int(11)     |
+-----------+-------------+

评论元:

|  column   |    type     |
+-----------+-------------+
| comment_id| int(11)     |
| key       | varchar(40) |
| value     | varchar(50) |
+-----------+-------------+

与 key comment meta 中的值 table (delete, reply_count, report) .

数据通过以下方式添加到表中:

• 当用户发表评论时，它会被添加到 comments 中数据库 parent_id设置为 0。
• 当用户为特定评论写回复时，它会添加到 comments 中数据库 parent_id设置为 comment_id的评论和 reply_count' is updated in the该评论的 comments_meta` 数据库。
• 当用户删除评论或回复时，comments_meta 中的值会更新数据库但设置 key至 delete和 value至 1 w.r.t 该评论或回复 ID，而不是将其从 comments 中删除数据库。

我只想获取所有未删除评论的列表。

到现在为止我试过这个:

select comments.comment_id,comments.comment, ifnull(comments_meta.value,0) as reply_count from comments left join comments_meta on comments_meta.comment_id = comments.comment_id and comments_meta.meta_key = "reply_count";

这给了我所有评论，包括已删除的评论。

是否可以使用此表模型获取列表？如何获取？或者我应该附加其中一个属性 delete或 'reply_count with评论`表？

示例数据注释:

|comment_id |    comment  |  parent_id  |  
+-----------+-------------+-------------+
|    1      | comment1    |      0      | 
|    2      | comment2    |      0      |
|    3      | reply1      |      1      |
|    4      | reply2      |      1      |
|    5      | comment3    |      0      |
+-----------+-------------+-------------+

示例数据注释元:

|comment_id |     key     |    value    |  
+-----------+-------------+-------------+
|    2      |   delete    |      1      | 
|    1      | reply_count |      2      |
+-----------+-------------+-------------+

预期输出:

|comment_id |    comment  | reply_count |  
+-----------+-------------+-------------+
|    1      |  comment1   |      2      | 
|    5      |  comment3   |      0      |
+-----------+-------------+-------------+

Answer 1

数据链接:

create table `comment`(
  `comment_id` int not null auto_increment,
  `comment` varchar(128),
  `parent_id` int not null,
  primary key(`comment_id`)
);


insert into `comment`(`comment_id`,`comment`,`parent_id`) values
(1,'comment1',0),
(2,'comment2',0),
(3,'reply1',1),
(4,'reply2',1),
(5,'comment3',0);

create table `comment_meta`(
  `comment_id` int not null,
  `key` varchar(128),
  `value` int not null,
  primary key(`comment_id`,`key`)
);

insert into `comment_meta`(`comment_id`,`key`,`value`) values
(2,'delete',1),
(1,'reply_count',2);

让我们只获取“根”注释:

  SELECT * FROM `comment` 
  WHERE `parent_id` = 0

让我们只选择评论:

  SELECT * FROM `comment` 
  WHERE `parent_id` != 0 

让我们加入(外部 - 列出所有根评论)这两个子查询，看看我们得到什么:

SELECT *
FROM
(
  SELECT * FROM `comment` 
  WHERE `parent_id` = 0
) AS `root_comments`
LEFT JOIN 
(
  SELECT * FROM `comment` 
  WHERE `parent_id` != 0  
) `replies` ON `replies`.`parent_id` = `root_comments`.`comment_id`;

我们得到类似 this 的信息:

现在让我们向结果集中添加一个额外的列，以显示该行是代表根评论 (0) 还是代表回复 (1):

SELECT *,IF(`replies`.`comment_id` IS NOT NULL, 1, 0)
FROM
(
  SELECT * FROM `comment` 
  WHERE `parent_id` = 0
) AS `root_comments`
LEFT JOIN 
(
  SELECT * FROM `comment` 
  WHERE `parent_id` != 0  
) `replies` ON `replies`.`parent_id` = `root_comments`.`comment_id`;

让我们选择已删除的评论:

SELECT * FROM `comment_meta`
WHERE `key` = 'delete'

将它们与我们目前拥有的结合起来，并添加一个 WHERE 条件以仅考虑那些在元表中没有带有“删除”键的对应行的评论:

WHERE `deleted_comments`.`comment_id` IS NULL

最后，我们只需要选择需要的列和GROUP BY评论/SUM()每个评论的回复(1):

请注意，在 MySQL 5.7 中，GROUP BY 默认模式已更改，如果不更改此设置，您将无法SUM 非分组列。

SELECT `root_comments`.`comment_id`,`root_comments`.`comment`,SUM(IF(`replies`.`comment_id` IS NOT NULL, 1, 0)) AS reply_count
FROM
(
  SELECT * FROM `comment` 
  WHERE `parent_id` = 0
) AS `root_comments`
LEFT JOIN 
(
  SELECT * FROM `comment` 
  WHERE `parent_id` != 0  
) `replies` ON `replies`.`parent_id` = `root_comments`.`comment_id`
LEFT JOIN 
(
  SELECT * FROM `comment_meta`
  WHERE `key` = 'delete'
) `deleted_comments` ON `deleted_comments`.`comment_id` = `root_comments`.`comment_id`
WHERE `deleted_comments`.`comment_id` IS NULL
GROUP BY `root_comments`.`comment_id`;

这是 link通过这个工作示例到 DBFiddle