题解 | #SQL 25.满足条件的用户的试卷完成数和题目练习数#

满足条件的用户的试卷完成数和题目练习数

http://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf

明确题意:

找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬,统计他们的2021年试卷完成数和题目练习数; 结果按试卷完成数升序,按题目练习数降序。


问题分解:

  • 找出高难度SQL试卷得分平均值大于80分的7级用户,生成子表t_user_id:
    • 内连接试卷作答表、试卷信息表、用户信息表:exam_record JOIN examination_info USING(exam_id) JOIN user_info USING(uid)
    • 筛选出目标用户:WHERE difficulty='hard' and tag='SQL' and level=7
    • 按用户分组:GROUP BY uid
    • 筛选平均分大于80的分组(用户):HAVING AVG(score)>80
  • 统计每个用户2021年的试卷完成数,生成子表t_exam_cnt:
    • 筛选作答时间和已完成:WHERE YEAR(submit_time)=2021 AND submit_time IS NOT NULL
    • 按用户分组:GROUP BY uid
    • 统计试卷完成数:count(exam_id) as exam_cnt
  • 统计每个用户2021年的试题作答数,生成子表t_question_cnt:
    • 筛选作答时间:WHERE YEAR(submit_time)=2021
    • 按用户分组:GROUP BY uid
    • 统计试题作答数:count(question_id) as question_cnt
  • 关联三张表:
    • 内连接t_user_id和t_exam_cnt,因为用户属性条件必须满足,题目又要求了只输出有试卷完成记录的用户:t_user_id JOIN t_exam_cnt USING(uid)
    • 左连接t_question_cnt:因为只要满足上面条件的结果,即使无试题作答记录也输出:LEFT JOIN t_question_cnt USING(uid)
  • 特殊处理试题作答数可能为0的情况(此时关联结果中试题作答数为NULL):IFNULL(question_cnt, 0) as question_cnt

细节问题:

  • 表头重命名:as
  • 按试卷完成数升序、题目练习数降序:ORDER BY exam_cnt, question_cnt desc

完整代码:

SELECT uid, exam_cnt, IFNULL(question_cnt, 0) as question_cnt
FROM (
    SELECT uid
    FROM exam_record
    JOIN examination_info USING(exam_id)
    JOIN user_info USING(uid)
    WHERE difficulty='hard' and tag='SQL' and `level`=7
    GROUP BY uid
    HAVING AVG(score)>80
) as t_user_id
JOIN (
    SELECT uid, count(exam_id) as exam_cnt
    FROM exam_record
    WHERE YEAR(submit_time)=2021 AND submit_time IS NOT NULL
    GROUP BY uid
) as t_exam_cnt
USING(uid)
LEFT JOIN (
    SELECT uid, count(question_id) as question_cnt
    FROM practice_record WHERE YEAR(submit_time)=2021
    GROUP BY uid
) as t_question_cnt
USING(uid)
ORDER BY exam_cnt, question_cnt desc;
SQL进阶 文章被收录于专栏

SQL进阶step by step

全部评论
文字部分写的很详细,谢谢
1 回复 分享
发布于 2023-01-13 14:54 北京
能帮忙看下为什么三张表直接连不可以,exam_id是错的 select er.uid, count( distinct(case when year(er.submit_time)='2021'and er.submit_time is not null then er.exam_id else null end)) as exam_cnt, count(distinct (case when year(pr.submit_time)='2021' then pr.id else null end)) as question_cnt from exam_record as er join examination_info as ei using(exam_id) left join practice_record as pr using(uid) left join user_info as ui using(uid) where ei.difficulty='hard' and ei.tag="SQL" and ui.level=7 group by er.uid having avg(er.score) >80 order by exam_cnt asc,question_cnt desc
点赞 回复 分享
发布于 2021-12-16 17:42
大佬你好!能否请问前两张表为什么不能直接合并为一张,即条件那里直接用Where submit_time is not null and difficulty = 'hard' and tag = 'SQL' and level = 7 and year(submit_time) = 2021
点赞 回复 分享
发布于 2022-04-17 17:08
为啥第一张子表要放前面,不能放在后面
点赞 回复 分享
发布于 2022-06-17 15:36

相关推荐

6面5kpi0oc的秋招小丑:是曲直,看行(列也行),全曲,全直,有曲有直各有一个
点赞 评论 收藏
分享
gcniz:一天写两千行你闹呢
点赞 评论 收藏
分享
25 3 评论
分享
牛客网
牛客企业服务