前言
练习sql语句,所有题目来自于力扣(https://leetcode.cn/problemset/database/)的免费数据库练习题。
今日题目:
1241.每个帖子的评论数
表:Submission
列名 | 类型 |
---|---|
sub_id | int |
parent_id | int |
编写一个解决方案以查找每个帖子的评论数。
结果表应包含帖子的 post_id 和对应的评论数 number_of_comments 。
Submissions 可能包含重复的评论。你应该统计出每个帖子的 唯一评论 的数目。
Submissions 可能包含重复的帖子。你应该将它们视为一个帖子。返回结果表应该按 post_id 升序排序。
我那不值一提的想法:
- 首先梳理表内容,题干一共给了一张表,记录了帖子id以及评论id
- 其次分析需求,需要找到每个帖子的评论数
- 首先需要找到帖子id
with post as (
select distinct sub_id
from Submissions
where parent_id is null
)
- 其次是要找到帖子对应的评论数量
count_a as (
select parent_id,count(distinct sub_id) as number_of_comments
from Submissions
where parent_id is not null
group by parent_id
)
- 最后第一张表左连接第二张表得到结果
with post as (
select distinct sub_id
from Submissions
where parent_id is null
)
,
count_a as (
select parent_id,count(distinct sub_id) as number_of_comments
from Submissions
where parent_id is not null
group by parent_id
)
select sub_id as post_id,ifnull(number_of_comments,0) as number_of_comments
from post p
left join count_a c
on p.sub_id = c.parent_id
order by post_id
结果:
总结:
能运行就行。