SQL统计学生优质帖数量 无优质帖时返回0的实现方案

涉及表结构 #

student(user_id, occupation)
thumbsup(user_id, post_id)
post(post_id, user_id)

统计规则 #

• 单帖收到至少1个点赞即判定为优质帖，多赞不重复计数
• 优质帖数量为用户发布的所有符合条件的帖子总数
• 无优质帖的学生也需要展示，对应数量显示为0

原有查询问题 #

原有查询使用隐式内连接关联学生表和优质帖统计结果，仅会返回存在优质帖的学生，无优质帖的学生会被直接过滤。

优化后查询语句 #

SELECT 
    S.user_id, 
    S.occupation, 
    COALESCE(temp2.goodpost, 0) AS goodpost
FROM swoosh.student S
LEFT JOIN (
    SELECT P.user_id, COUNT(P.post_id) as goodpost
    FROM swoosh.post P
    INNER JOIN (
        SELECT T.post_id
        FROM swoosh.thumbsup T
        GROUP BY T.post_id
    ) TEMP ON P.post_id = TEMP.post_id
    GROUP BY P.user_id
) temp2 ON S.user_id = temp2.user_id

逻辑说明 #

1. 以student表作为主表，通过LEFT JOIN关联优质帖统计结果，保证所有学生都保留在结果集中
2. 内层点赞统计子查询仅需返回有过点赞的post_id，不需要返回具体点赞数，也可加HAVING COUNT(*) >=1进一步过滤无赞帖子提升效率
3. 使用COALESCE函数将无优质帖学生对应的goodpost空值转换为0，符合展示要求

内容的提问来源于stack exchange，提问作者randomNameGenerator