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

# select uid,exam_cnt,if(question_cnt is null,0,question_cnt) question_cnt
# from(
# 	select 
# 	    er.uid,
# 	    count(submit_time) as exam_cnt
# 	from exam_record er
# 	where YEAR(submit_time) = 2021
# 	group by uid
# 	) a
# left join 
# 	(
# 	select
# 	    pr.uid,
# 	    count(submit_time) as question_cnt
# 	from practice_record pr
# 	where YEAR(submit_time) = 2021
# 	group by uid
# 	) b
# 	using(uid)
# where uid in
# 	(
# 	select er.uid
# 	from user_info ui inner join exam_record er 
# 		on ui.uid = er.uid 
# 		and `level` = 7	# 7级的红名大佬
# 		inner join examination_info ei 
# 		on ei.exam_id = er.exam_id 
# 		and difficulty = 'hard'	# 高难度
# 		and tag = 'SQL'	# SQL试卷
# 	group by er.uid 
# 	having avg(score) > 80	# 得分平均值大于80
# 	)
# order by exam_cnt,question_cnt desc;# 结果按试卷完成数升序，按题目练习数降序

select uid,sum(if(试卷 = '试卷',exam_cnt,0)) exam_cnt,
	sum(if(试卷 = '题目',exam_cnt,0)) question_cnt
from(
	select uid,count(exam_id) exam_cnt,'试卷' from exam_record
	where year(submit_time) = 2021 group by uid
	union 
	select uid,count(question_id) question_cnt,'题目' from practice_record
	where year(submit_time) = 2021 group by uid
	) as 合并数据	
where uid in (	# 找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬
	select er.uid
	from user_info ui inner join exam_record er 
		on ui.uid = er.uid 
		and `level` = 7	# 7级的红名大佬
		inner join examination_info ei 
		on ei.exam_id = er.exam_id 
		and difficulty = 'hard'	# 高难度
		and tag = 'SQL'	# SQL试卷
	group by er.uid 
	having avg(score) > 80	# 得分平均值大于80
	)
group by uid
order by exam_cnt,question_cnt desc; # 结果按试卷完成数升序，按题目练习数降序