1241 每个帖子的评论数（自己写出）

最新推荐文章于 2024-05-27 15:21:43 发布

每天都要被自己菜醒

最新推荐文章于 2024-05-27 15:21:43 发布

阅读量165

点赞数

分类专栏： leetcode 数据库刷题文章标签： mysql

本文链接：https://blog.csdn.net/qq_45531594/article/details/108412690

版权

数据库刷题同时被 2 个专栏收录

38 篇文章 3 订阅

订阅专栏

leetcode

21 篇文章 0 订阅

订阅专栏

本文介绍如何使用MySQL查询来统计每个帖子的评论数量，包括关键的SQL语句和可能涉及的数据表结构分析。

摘要由CSDN通过智能技术生成

Create table If Not Exists Submissions (sub_id int, parent_id int)
Truncate table Submissions
insert into Submissions (sub_id, parent_id) values ('1', 'None')
insert into Submissions (sub_id, parent_id) values ('2', 'None')
insert into Submissions (sub_id, parent_id) values ('1', 'None')
insert into Submissions (sub_id, parent_id) values ('12', 'None')
insert into Submissions (sub_id, parent_id) values ('3', '1')
insert into Submissions (sub_id, parent_id) values ('5', '2')
insert into Submissions (sub_id, parent_id) values ('3', '1')
insert into Submissions (sub_id, parent_id) values ('4', '1')
insert into Submissions (sub_id, parent_id) values ('9', '1')
insert into Submissions (sub_id, parent_id) values ('10', '2')
insert into Submissions (sub_id, parent_id) values ('6', '7')

在这里插入图片描述

#空值ifnull
select a.sub_id  as 'post_id',ifnull(num,0) as 'number_of_comments'
from
    #1.把帖子找出来
    (select sub_id
    from Submissions 
    where parent_id is null
    group by sub_id ) a
left join 
    #2.评论数计数
    (select   parent_id,count(distinct  sub_id) as 'num'
    from  Submissions
    where  parent_id  is  not null
    group by parent_id)b on a.sub_id = b.parent_id
order by  post_id