通过代码

SELECT
    t20.tag,
    t20.exam_cnt exam_cnt_20,
    t21.exam_cnt exam_cnt_21,
    concat(round((t21.exam_cnt - t20.exam_cnt)*100/t20.exam_cnt,1 ),'%') growth_rate,
    t20.ranking exam_cnt_rank_20,
    t21.ranking exam_cnt_rank_21,
    CAST(t21.ranking AS SIGNED) - CAST(t20.ranking AS SIGNED) rank_delta
FROM
(SELECT
     tag,
     '2020' start_year,
     count(*) exam_cnt,
     rank()over(
         order by count(*) desc
         ) ranking

 FROM(select
          uid,
          e_r.exam_id,
          if(DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202001 and 202006,1,0) time1,
          tag,
          submit_time
      FROM
          exam_record e_r
              RIGHT JOIN
          examination_info e_i
          ON
                  e_r.exam_id = e_i.exam_id
      WHERE
          DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202001 and 202006
         or
          DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202101 and 202106) tim
 WHERE
         time1 = 1
 GROUP BY
     tag)  t20

JOIN(SELECT
     tag,
     '2021' start_year,
     count(*) exam_cnt,
     rank()over(
         order by count(*) desc
         ) ranking
 FROM(select
          uid,
          e_r.exam_id,
          if(DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202001 and 202006,1,0) time1,
          tag,
          submit_time
      FROM
          exam_record e_r
              RIGHT JOIN
          examination_info e_i
          ON
                  e_r.exam_id = e_i.exam_id
      WHERE
          DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202001 and 202006
         or
          DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202101 and 202106) tim
 WHERE
         time1 = 0
 GROUP BY
     tag)  as t21
            ON
                    t20.tag = t21.tag
ORDER BY
    growth_rate desc,
    exam_cnt_rank_21 desc

别看这么长的代码，其中很多都是重复的，没办法判题系统不接受视图

而且，众所周知啊，同样的一道题，代码越长基本思路就越简单，理解起来就越容易

思路

又是一个乱七八糟条件一堆的题目嗷，但是题目贴心的给了个解释表，就让这个题变得清晰起来了，我再贴一下这个解释表：

alt

然后拆解题目：计算2021年上半年各类试卷的做完次数相比2020年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。

1.2021上半年、2020同期

2.总完成数，排名，增长率(百分比，1位小数)，名次变化

那么其实这道题还是很简单的对吧？

怎么分别筛选出来2020上半年跟2021上半年呢？

筛选2020 2021 上半年

select
    uid,
    e_r.exam_id,
    if(DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202001 and 202006,1,0) time1,
    tag,
    submit_time
FROM
    exam_record e_r
 RIGHT JOIN
    examination_info e_i
 ON
            e_r.exam_id = e_i.exam_id
WHERE
    DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202001 and 202006
   or
    DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202101 and 202106

有人建议我贴上结果，但是直接在网页上写的，它没有字段名，所以看着也很难看，实在想看建议直接自己复制运行/doge

话接上文：

怎么分别筛选出来2020上半年跟2021上半年呢？

啊对，就是between，直接where筛选就行了，这里有一点值得注意的是between区间是闭合的也就是[]

那么怎么分别筛选呢？我们之前做过类似的啊，直接**if(DATE_FORMAT(submit_time,'%Y%m') BETWEEN 202001 and 202006,1,0) **

打上标记就行了，打完之后2020上半年自加字段time1 是1，2021年是0

之所以可以这样来，是我已经用where筛选过上半年的了

这个时候题目上的那个解释表就可以做出来了，但是我选择代码更长但是结构可能结构清晰一点，也就是筛成两个表

题目要求其实都是根据2020、2021年分类的做题总数、分类排名算出的，所以我们下一步就是

2020、2021分类做题数目、排名

#2.1代码
SELECT
     tag,
     count(*) exam_cnt,
     rank()over(
         order by count(*) desc
         ) ranking
 FROM(1.代码) tim
 WHERE
         time1 = 1
 GROUP BY
     tag

在上一个表的基础上进行查找，time1 是 1 那就是 2020上半年的，整理成为一个表分类count(*)就行

这里用了窗口函数rank， rank的逻辑我们之前说过，现在再来一次加深印象：1、2、2、3，还是不知道可以看看前面的内容：题解 | #近三个月未完成试卷数为0的用户完成情况#

还有窗口函数不是一定要加上partition by的

2021的也一样，就是把time1 = 1改成0就行

#2.2代码
SELECT
     tag,
     count(*) exam_cnt,
     rank()over(
         order by count(*) desc
         ) ranking
 FROM(1.代码) tim
 WHERE
         time1 = 0
 GROUP BY
     tag

计算题目需求、排序

SELECT
    t20.tag,
    t20.exam_cnt exam_cnt_20,
    t21.exam_cnt exam_cnt_21,
    concat(round((t21.exam_cnt - t20.exam_cnt)*100/t20.exam_cnt,1 ),'%') growth_rate,
    t20.ranking exam_cnt_rank_20,
    t21.ranking exam_cnt_rank_21,
    CAST(t21.ranking AS SIGNED) - CAST(t20.ranking AS SIGNED) rank_delta
FROM
(2.1.代码)  t20
JOIN(2.2.代码)  as t21
 ON
      t20.tag = t21.tag
ORDER BY
    growth_rate desc,
    exam_cnt_rank_21 desc

题目上说，只有两个时间段都做过的类型、也就第二步我们筛选出的两个表中都有的tag才会出现在答案里所以：内连接

alt

20、21分类做题数目、排名在第二部分我们已经完成，所以要求的只有：growth_rate \rank_delta

growth_rate要求百分数保留一位小数，我们就将除法结果放大100倍，round取1位小数，concat将结果加上%

然后就是最后的rank_delta

本来我还想这道题也不难为什么通过率这么低：

alt

然后提交代码发现确实有问题：

alt

这是啥呢？

就是说这窗口函数排序出来的排名相减可能会出现负数也就是2021比2020排名高了，但是上边计算rate也可能出现负数为什么没报错呢？

如果两个数有一个是UNSIGNED INT类型，他们相减的结果也是UNSIGNED INT类型，而如果这个结果是负数：

在MySQL 5.5.5之前，结果变成了最大的整数（18446744073709551615）

从MySQL 5.5.5开始，这种情况会返回一个错误：BIGINT UNSIGNED value is out of range

窗口函数排名都是UNSIGNED INT类型

所以我们强制转换

CAST(t21.ranking AS SIGNED) - CAST(t20.ranking AS SIGNED) rank_delta

最后排序、完事。

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

通过代码

思路

筛选2020 2021 上半年

2020、2021分类做题数目、排名

计算题目需求、排序