思路一：

分析：

既然最后只需要得到2020和2021年都有作答记录的tag，

不妨分别筛选出来2020年和2021年的数据再用join连接.

2020、2021各自的作答情况去对应的表中取即可

实现：

select q1.tag,
q1.exam_cnt as exam_cnt_20,
q2.exam_cnt as exam_cnt_21,
concat(round((q2.exam_cnt-q1.exam_cnt)*100/q1.exam_cnt,1),'%') 
as growth_rate,
q1.exam_cnt_rank as exam_cnt_rank_20,
q2.exam_cnt_rank as exam_cnt_rank_21,
cast(q2.exam_cnt_rank as signed)-cast(q1.exam_cnt_rank as signed) --易错点1
as rank_delta
from 
    (select tag,'2020' as start_year,
    count(submit_time) as exam_cnt,
     rank() over (order by count(submit_time) desc) as exam_cnt_rank --易错点2
     from exam_record as er
     join examination_info as ei
     on er.exam_id=ei.exam_id
     where start_time between '2020-01-01 00:00:00' 
     and '2020-06-30 23:59:59'
    group by tag) as q1
join
    (select tag,'2021' as start_year,
    count(submit_time) as exam_cnt,
     rank() over (order by count(submit_time) desc) as exam_cnt_rank --易错点2
     from exam_record as er
     join examination_info as ei
     on er.exam_id=ei.exam_id
     where start_time between '2021-01-01 00:00:00' 
     and '2021-06-30 23:59:59'
    group by tag) as q2
on q1.tag=q2.tag 
where q1.exam_cnt!=0 and q2.exam_cnt!=0 --易错点3
order by growth_rate desc,exam_cnt_rank_21 desc

易错点：

• 计算名次变化时q2.exam_cnt_rank-q1.exam_cnt_rank as rank_delta会得到负数，报错：“BIGINT UNSIGNED值超出范围”。解决办法——cast(q2.exam_cnt_rank as signed)-cast(q1.exam_cnt_rank as signed) as rank_delta
• 分别筛选并排序时不应有partition by 部分，尤其不应partition by tag，不然每个tag得到的名次都为1
• 为确保所得到的tag中有完成作答记录，应避免取到exam_cnt=0的情况，故最后应用where语句加以筛选——where q1.exam_cnt!=0 and q2.exam_cnt!=0

思路二：

分析：

• 按照题目解释得到对应表格——rank()over(partition by start_year)
• 取2020、2021年数据时要借助if分别得到
• tag需要同时具有2020、2021作答记录——count(tag)=2

实现：

select tag,
sum(if(start_year=2020,exam_cnt,0)) as exam_cnt_20,
sum(if(start_year=2021,exam_cnt,0)) as exam_cnt_21,
concat(round(sum(if(start_year=2021,exam_cnt,-exam_cnt))/sum(
    if(start_year=2020,exam_cnt,0))*100,1),'%') as growth_rate,
sum(if(start_year=2020,exam_cnt_rank,0)) as exam_cnt_rank_20,
sum(if(start_year=2021,exam_cnt_rank,0)) as exam_cnt_rank_21,
sum(if(start_year=2020,-exam_cnt_rank,exam_cnt_rank)) as rank_delta
from
    (select tag,year(submit_time) as start_year,
    count(submit_time) as exam_cnt,
     rank()over (partition by year(submit_time) order by 
                count(submit_time) desc) as exam_cnt_rank
     from exam_record as er
     join examination_info as ei
     on er.exam_id=ei.exam_id
     where (year(submit_time)=2020 or year(submit_time)=2021)
     and month(submit_time)<=6
     group by tag,start_year) as q
group by tag
having count(tag)=2
order by growth_rate desc,exam_cnt_rank_21 desc