题解 | #审题分解+分步骤求解#

一、审题分解：

1. tag = SQL;
2. difficult = hard;
3. score > 80;
4. level = 7;
5. 2021年试卷总完成次数：count（submit_time）,submit_time is not null;
6. 2021年题目总练习次数：count(submit_time);
7. order by 试卷,题目 desc

不能一次性求出，所以分步骤

二、分步骤求解

1、2021年试卷总完成次数：

sum(if(submit_time is not null,1,0)) as exam_cnt
from exam_record
where year(submit_time)=2021
group by uid

2、2021年题目总练习次数：

count(submit_time) as question_cnt
from practice_record
where year(submit_time)=2021
group by uid

3、被限制的用户

from exam_record a join examination_info b
on a.exam_id=b.exam_id
join user_info c on a.uid=c.uid
where tag='SQL'and difficulty='hard'and level=7
group by uid
having avg(score)>=80

三、开始组装代码

select cc.uid,ifnull(aa.exam_cnt,0) ex_cnt,ifnull(bb.question_cnt,0) qu_cnt
from
(select uid,
sum(if(submit_time is not null,1,0)) as exam_cnt
from exam_record
where year(submit_time)=2021
group by uid)aa

left join (select uid,
count(submit_time) as question_cnt
from practice_record
where year(submit_time)=2021
group by uid)bb on aa.uid=bb.uid 

right join (select a.uid
from exam_record a join examination_info b
on a.exam_id=b.exam_id
join user_info c on a.uid=c.uid
where tag='SQL'and difficulty='hard'and level=7
group by uid
having avg(score)>=80)cc on aa.uid=cc.uid 

order by ex_cnt ,qu_cnt desc