题解 | #审题分解+分步骤求解#
满足条件的用户的试卷完成数和题目练习数
http://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf
一、审题分解：
- tag = SQL;
- difficult = hard;
- score > 80;
- level = 7;
- 2021年试卷总完成次数：count（submit_time）,submit_time is not null;
- 2021年题目总练习次数：count(submit_time);
- 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
