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

SELECT a.uid,exam_cnt,ifnull(question_cnt,0)
FROM (
SELECT distinct(exam_record.uid)
FROM exam_record 
LEFT JOIN examination_info ON examination_info.exam_id =exam_record.exam_id
LEFT JOIN user_info ON user_info.uid = exam_record.uid
WHERE level = 7 and tag='SQL' and difficulty='hard' and score is not null
group by exam_record.uid
having avg(score)>80) a

LEFT JOIN (

SELECT uid,count(submit_time) exam_cnt
FROM exam_record
WHERE YEAR(submit_time) = '2021' and submit_time is not null
group by uid) b ON a.uid =  b.uid

LEFT JOIN (

SELECT uid,count(submit_time) question_cnt
FROM practice_record
WHERE YEAR(submit_time) = '2021' and submit_time is not null
group by uid) c ON c.uid = a.uid

order by exam_cnt ,question_cnt desc

思路：生成三张表进行左连接，三张表分别是：满足要求的uid，考试卷子每个uid的答题次数和练习卷子每个uid的做题次数，以uid表进行左联，当uid为1001，1002 并且另两张表的uid不存在1001或者1002时，左连接可以出现null，以0在结果显示出来。

最开始思路：得到满足要求的uid，在每张表中通过子查询得到uid，和答题次数再将两张表进行连接，会出现当某一张表没有满足要求的uid时结果出现确实，即结果为0的行展示不出来