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

# 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，
# 统计他们的2021年试卷总完成次数和题目总练习次数，
# 只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。
# full join 在mysql中是left join union right join
select * from
((select if(t1.uid is null, t2.uid, t1.uid) as uid,
if(exam_cnt is null, 0, exam_cnt) as exam_cnt,
if(question_cnt is null, 0, question_cnt) as question_cnt
from
	((select uid, count(*) as exam_cnt
	from exam_record 
	where uid in
		(select uid
		from exam_record a left join examination_info b 
		on a.exam_id = b.exam_id
		where tag = 'SQL' and difficulty = 'hard'
		and uid in (select uid from user_info
					where level = 7) 
		and year(a.submit_time) = '2021'
		group by uid
		having avg(score) >= 80)
	and year(submit_time) = '2021'
	group by uid)t1
	left join
	(select uid, count(*) as question_cnt
	from practice_record 
	where uid in
		(select uid
		from exam_record a left join examination_info b 
		on a.exam_id = b.exam_id
		where tag = 'SQL' and difficulty = 'hard'
		and uid in (select uid from user_info
					where level = 7) 
		and year(a.submit_time) = '2021'
		group by uid
		having avg(score) >= 80)
	and year(submit_time) = '2021'
	group by uid)t2
	on t1.uid = t2.uid))
union 
(select if(t1.uid is null, t2.uid, t1.uid) as uid,
if(exam_cnt is null, 0, exam_cnt) as exam_cnt,
if(question_cnt is null, 0, question_cnt) as question_cnt
from
	((select uid, count(*) as exam_cnt
	from exam_record 
	where uid in
		(select uid
		from exam_record a left join examination_info b 
		on a.exam_id = b.exam_id
		where tag = 'SQL' and difficulty = 'hard'
		and uid in (select uid from user_info
					where level = 7) 
		and year(a.submit_time) = '2021'
		group by uid
		having avg(score) >= 80)
	and year(submit_time) = '2021'
	group by uid)t1
	right join
	(select uid, count(*) as question_cnt
	from practice_record 
	where uid in
		(select uid
		from exam_record a left join examination_info b 
		on a.exam_id = b.exam_id
		where tag = 'SQL' and difficulty = 'hard'
		and uid in (select uid from user_info
					where level = 7) 
		and year(a.submit_time) = '2021'
		group by uid
		having avg(score) >= 80)
	and year(submit_time) = '2021'
	group by uid)t2
	on t1.uid = t2.uid))
    )T
order by exam_cnt asc, question_cnt desc