题解 | #试卷完成数同比2020年的增长率及排名变化#
试卷完成数同比2020年的增长率及排名变化
https://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b
萌新解题思路。
1.求出每年，每个科目的的完成数（虚拟表）
2.在读取虚拟表去，得到想要的数据，
with tiaojian as (
select
ef.tag, #科目
year(ed.start_time) as years,#提取科目的那一年
count(ed.id) as pcnt,#提取完成的数量，因WHERE判断了，分数(score)不为空，则一定是完成的
rank()over(partition by year(ed.start_time) order by
count(ed.id) desc) as pt #对于每年，完成完成科目总数的降序排序
from exam_record ed left join examination_info ef
on ed.exam_id=ef.exam_id
where
month(ed.start_time)<=6#上半年也就是说，月份小于等于六就可以了
and
ed.score is not null#判断必须都完成了，所以，有分肯定完成了
group by years,ef.tag#按照每年和每个科目分组，去判断
)
select
tag,
max(case when years=2020 then pcnt end),#当年份2020年时候，取2020年完成值
max(case when years=2021 then pcnt end),#当年份2021年时候，取2020年完成值
concat(
round(
(max(case when years=2021 then pcnt end)/max(case when years=2020 then pcnt end)-1
)*100,1),"%") pzzl, #求公式是今年的完成量除以去年完成量-1就是增长率，整体括号乘以100，round截取小数点后一位，concat加上%号
sum(case when years=2020 then pt end) #当年份2020时，取2020时的排名,
sum(case when years=2021 then pt end) as pct, #当年份2021时，取2021时的排名
sum(case when years=2021 then pt end)-sum(case when years=2020 then pt end) #2021排名-2020排名，就全部完成了。
from tiaojian
group by tag #先分组，因题目要求，必须2年都有数据的，也就是说科目(tag)大于1 就等于2年都有数据了
having count(*)>1
order by pzzl desc,pct desc#按要求排序
select
ef.tag, #科目
year(ed.start_time) as years,#提取科目的那一年
count(ed.id) as pcnt,#提取完成的数量，因WHERE判断了，分数(score)不为空，则一定是完成的
rank()over(partition by year(ed.start_time) order by
count(ed.id) desc) as pt #对于每年，完成完成科目总数的降序排序
from exam_record ed left join examination_info ef
on ed.exam_id=ef.exam_id
where
month(ed.start_time)<=6#上半年也就是说，月份小于等于六就可以了
and
ed.score is not null#判断必须都完成了，所以，有分肯定完成了
group by years,ef.tag#按照每年和每个科目分组，去判断
)
select
tag,
max(case when years=2020 then pcnt end),#当年份2020年时候，取2020年完成值
max(case when years=2021 then pcnt end),#当年份2021年时候，取2020年完成值
concat(
round(
(max(case when years=2021 then pcnt end)/max(case when years=2020 then pcnt end)-1
)*100,1),"%") pzzl, #求公式是今年的完成量除以去年完成量-1就是增长率，整体括号乘以100，round截取小数点后一位，concat加上%号
sum(case when years=2020 then pt end) #当年份2020时，取2020时的排名,
sum(case when years=2021 then pt end) as pct, #当年份2021时，取2021时的排名
sum(case when years=2021 then pt end)-sum(case when years=2020 then pt end) #2021排名-2020排名，就全部完成了。
from tiaojian
group by tag #先分组，因题目要求，必须2年都有数据的，也就是说科目(tag)大于1 就等于2年都有数据了
having count(*)>1
order by pzzl desc,pct desc#按要求排序
查看9道真题和解析