SQL141 试卷完成数同比2020年的增长率及排名变化1、首先计算出上半年每个tag的作答记录以及排名2、因为只能返回具有同比的tag记录,故可以用count()>=2筛选出tag,然后在聚合函数中使用case实现将行转为列,将20年的作答数和排名和21年的作答数和排名合并成一列3、最后进行对应的计算即可with t1 as(select tag,year(start_time) start_year,count(exam_record.exam_id) exam_cnt ,rank() over(partition by year(start_time) order by year(start_time) asc,count(exam_record.exam_id) desc) exam_cnt_rankfrom exam_record left join examination_info on examination_info.exam_id=exam_record.exam_idwhere submit_time is not null and month(submit_time)<=6group by tag,year(start_time)),t2 as(select tag,sum(case when start_year=2020 then exam_cnt end ) exam_cnt_20 ,sum(case when start_year=2021 then exam_cnt end ) exam_cnt_21 ,sum(case when start_year=2020 then exam_cnt_rank end ) exam_cnt_rank_20 ,sum(case when start_year=2021 then exam_cnt_rank end ) exam_cnt_rank_21from t1where tag in (select tag from t1 group by tag having count(tag)>=2)group by tag)select tag,exam_cnt_20,exam_cnt_21 ,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1),'%') growth_rate ,exam_cnt_rank_20,exam_cnt_rank_21 ,exam_cnt_rank_21-exam_cnt_rank_20 rank_deltafrom t2order by round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1) desc,exam_cnt_rank_21 desc