题解 | #试卷完成数同比2020年的增长率及排名变化#

试卷完成数同比2020年的增长率及排名变化

https://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b

select
t.tag,
sum(case when t.years=2020 then t.m end),
sum(case when t.years=2021 then t.m end),
concat(
round(
((sum(case when t.years=2021 then t.m end)/sum(case when t.years=2020 then t.m end))-1)*100,1),"%") as zzl,
sum(case when t.years=2020 then t.d end),
sum(case when t.years=2021 then t.d end) as pm,
sum(case when t.years=2021 then t.d end)-
sum(case when t.years=2020 then t.d end)
from(
select
ef.tag,
year(ed.submit_time) as years,
sum(case when ed.submit_time is not null then 1 else 0 end) as m,
rank()over(partition by year(ed.submit_time) order by sum(case when ed.submit_time is not null then 1 else 0 end) desc) as d,
count(*)over(partition by ef.tag) as py
from exam_record ed left join examination_info ef 
on ed.exam_id=ef.exam_id
where
month(ed.submit_time)<=6
and
ed.submit_time is not null
group by ef.tag,years
) as t 
where
t.py>=2
group by t.tag
order by zzl desc,pm desc

全部评论

相关推荐

陈逸轩1205:才105 哥们在养生呢
点赞 评论 收藏
分享
醉蟀:你不干有的是人干
点赞 评论 收藏
分享
评论
1
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务