# 统计每类试卷在2020年和2021年上半年的做完次数及做完次数排名
select tag,
sum(if(submit_time < '2020-07-01 00:00:00' and year(submit_time) = '2020', 1,0)) as exam_cnt_20,
sum(if(submit_time < '2021-07-01 00:00:00' and year(submit_time) = '2021', 1,0)) as exam_cnt_21,
rank()over(order by sum(if(submit_time < '2020-07-01 00:00:00' and year(submit_time) = '2020', 1,0)) desc) as exam_cnt_rank_20,
rank()over(order by sum(if(submit_time < '2021-07-01 00:00:00' and year(submit_time) = '2021', 1,0)) desc) as exam_cnt_rank_21
from examination_info ei 
join (select * from exam_record where submit_time is not null) k
on ei.exam_id = k.exam_id
group by tag


# -----------------------------------完整代码------------------------------------
select tag, exam_cnt_20, exam_cnt_21,
concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') as growth_rate,
exam_cnt_rank_20, exam_cnt_rank_21, CONVERT(exam_cnt_rank_21, SIGNED)-CONVERT(exam_cnt_rank_20, SIGNED) as rank_delta
from (
    select tag,
    sum(if(submit_time < '2020-07-01 00:00:00' and year(submit_time) = '2020', 1,0)) as exam_cnt_20,
    sum(if(submit_time < '2021-07-01 00:00:00' and year(submit_time) = '2021', 1,0)) as exam_cnt_21,
    rank()over(order by sum(if(submit_time < '2020-07-01 00:00:00' and year(submit_time) = '2020', 1,0)) desc) as exam_cnt_rank_20,
    rank()over(order by sum(if(submit_time < '2021-07-01 00:00:00' and year(submit_time) = '2021', 1,0)) desc) as exam_cnt_rank_21
    from examination_info ei 
    join (select * from exam_record where submit_time is not null) k
    on ei.exam_id = k.exam_id
    group by tag
) k1
where exam_cnt_20>0 and exam_cnt_21>0
order by growth_rate desc, exam_cnt_rank_21 desc;

1.注意这里需要统计各类试卷的完成次数，因此不能直接通过筛选submit_time is not null去获取试卷完成记录再分组，这样会导致没有试卷完成记录的试卷类被忽视，从而得不到这类试卷的完成次数。（最好是获得全部种类试卷的完成次数之后，再去根据实际需求进行筛选计算）

2.直接使用排名数相减，会导致查询时两个无符号的字段相减,得到一个负数,会抛出该异常：BIGINT UNSIGNED value is out of range in ....

这时需要用到格式转换将无符号类型转换成有符号类型：select (CONVERT(num1, SIGNED)-CONVERT(num1, SIGNED))

3.用好sum()与if()函数联合使用从而满足需求真的很棒！

4.窗口函数的使用，及rank()与row_number()函数的区别都需要弄清楚哦！