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

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

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

#高难度SQL试卷得分平均值大于80并且是7级的红名大佬 2021年 完成次数 和题目练习次数
#不能把四个表连起来 因为试卷id 和 题目id 是没关联的
select ui.uid, COUNT(er.submit_time) AS exam_cnt,
#题目练习总次数
COALESCE(
    (select COUNT(question_id)
    FROM practice_record AS pr
    WHERE pr.uid = ui.uid AND YEAR(submit_time) = '2021' 
    ),0
)AS question_cnt
FROM user_info AS ui 
JOIN exam_record AS er using(uid)
JOIN examination_info AS ei using(exam_id)
WHERE ui.level = 7 AND YEAR(er.start_time) = '2021' #7级并且是2021年的记录
GROUP BY ui.uid
#高难度SQL试卷得分平均值大于80
HAVING exam_cnt != 0 AND AVG(if(tag = 'SQL' AND difficulty = 'hard',score,NULL)) > 80
ORDER BY exam_cnt,question_cnt DESC

题目思路分析：本题要求统计高难度SQL试卷得分平均值大于80并且是7级的红名大佬的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

首先，筛选的列 uid, 试卷总完成次数题目总练习次数

先看试卷总完成次数，我们可以把 user_info exam_record examination_info 这几个表连接，就有对应的tag和difficulty,level

条件1 7级 2021年 ui.level = 7 AND YEAR(er.start_time) = '2021'

肯定要对uid进行进行分组，那高难度SQL试卷得分平均值大于80 这个条件可以放到having后面

HAVING exam_cnt != 0 AND AVG(if(tag = 'SQL' AND difficulty = 'hard',score,NULL)) > 80

再求题目总练习次数，这个在practice_record表中，可以构建子查询，用pr.uid = ui.uid 限定条件，外部查询中的 ui.uid 和子查询中的 pr.uid 关联起来，确保了问题数量与每个用户相关联。

(select COUNT(question_id) FROM practice_record AS pr WHERE pr.uid = ui.uid AND YEAR(submit_time) = '2021' GROUP BY uid)：对于每个用户，统计 practice_record 表中满足条件的问题数量，并将结果存储为 question_cnt。

错误做法，把tag = 'SQL' AND difficulty = 'hard' 放在where里面

select ui.uid, COUNT(er.submit_time) AS exam_cnt,
#题目练习总次数
COALESCE(
    (select COUNT(question_id)
    FROM practice_record AS pr
    WHERE pr.uid = ui.uid AND YEAR(submit_time) = '2021' 
    ),0
)AS question_cnt
FROM user_info AS ui 
JOIN exam_record AS er using(uid)
JOIN examination_info AS ei using(exam_id)
WHERE ui.level = 7 AND YEAR(er.start_time) = '2021' #7级并且是2021年的记录
AND tag = 'SQL' AND difficulty = 'hard'
GROUP BY ui.uid
#高难度SQL试卷得分平均值大于80
HAVING exam_cnt != 0 AND AVG(score) > 80
ORDER BY exam_cnt,question_cnt DESC

这样子会直接过滤掉其他的考试记录，后面在COUNT的时候就会变少了

子查询的方式

select uid, COUNT(er.submit_time) AS exam_cnt,
#题目练习总次数
COALESCE(
    (select COUNT(pr.submit_time) FROM practice_record AS pr
    WHERE pr.uid = er.uid AND YEAR(pr.submit_time) = '2021')
,0) AS question_cnt
FROM exam_record AS er
WHERE uid in(
    select uid FROM user_info AS ui
    WHERE ui.level = 7
)
AND YEAR(er.start_time) = '2021' #7级并且是2021年的记录

GROUP BY uid                        
#高难度SQL试卷得分平均值大于80
HAVING  exam_cnt != 0 AND (
    select AVG(score) FROM exam_record
    WHERE exam_id in(
        select exam_id FROM examination_info
        WHERE tag = 'SQL'  AND difficulty = 'hard'
    ) AND submit_time is NOT NULL AND uid = er.uid
) > 80  #这个可以放在where后面
ORDER BY exam_cnt,question_cnt DESC

限制高难度SQL试卷得分平均值大于80的用户，需要确保子查询只计算特定用户的平均分数。可以通过将子查询关联到外部查询，以便它只计算当前用户的平均分数。uid = er.uid

其他帖子的题解

#先统计2021年的答题数量和完成试卷数量 再对uid进行限制
select uid, exam_cnt,
if(question_cnt is NULL ,0 ,question_cnt)
FROM (
    select uid, COUNT(submit_time) AS exam_cnt
    FROM exam_record
    WHERE YEAR(submit_time) = '2021'
    GROUP BY uid
) AS t1
LEFT JOIN (
    select uid, COUNT(submit_time) AS question_cnt
    FROM practice_record
    WHERE YEAR(submit_time) = '2021'
    GROUP BY uid
) AS t2 using(uid)
WHERE uid in(
    select uid FROM user_info AS ui
    JOIN exam_record AS er using(uid)
    JOIN examination_info AS ei using(exam_id) 
    WHERE tag = 'SQL' AND difficulty = 'hard' AND level = 7
    GROUP BY uid
    HAVING avg(score) > 80
)
order by exam_cnt asc, question_cnt desc