Mysql5.7分组后生成组内排名

背景

写一个报表sql的时候遇到一个场景，有个项目分数表格，每个项目会打多次分数，那么需要取最新的四次打分，并且行转列，mysql中我使用了最常见的行转列的方法，通过case when条件判断来转，那么这个行转列中一个必要条件就是排名了，相当于我要给每个项目的四次打分生成一个排名，类似1、2、3、4这样子。 看似很简单的一个需求确实难倒我了，所以特地写个blog记录一下。

表结构

表结构大概长这个样子，很简单，有个创建时间、项目id、分数，这是我们需要用到的三个字段。

CREATE TABLE `devops_project_score` (
  `create_by` bigint(20) DEFAULT '0' COMMENT '创建人',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `project_id` varchar(100) DEFAULT NULL COMMENT '项目id',
  `score_all` int(8) DEFAULT NULL COMMENT '总分',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COMMENT='项目分数表'

思路

1. 项目打分数据按项目分组
2. 项目打分数据按时间倒序取前四
3. 项目打分数据前四生成排名
4. 通过case when 判断生成的排名数据进行行专列

具体实现

通过变量查询时生成排名数据
按照思路来，按项目分组时间倒序并且生成排名取时间倒序的四条。那么问题来了，如何生成排名呢，这时候我想到了可以用到变量，于是用变量简单生成了一下排名先。

select @rownum:=@rownum+1 as rownum, l.project_id,l.create_time,l.score_all
from devops_project_score l, ( SELECT @rownum := 0 ) t order by project_id,create_time desc ;

细心的你可以能已经想到了，那我分组之后如何给出排名呢？这里确实是个问题，我也没有思路了。况且这个sql我是准备作为一个视图，还需要和其他表关联，那么作为一个视图的话，生成视图的sql中是不允许有变量的，这里要画重点哦。所以灵机一动，我在想是不是能拆分一下步骤，先把排名前四的数据取出来，然后再想法通过非变量的形式生成排名?

1.拆分步骤，按分组时间倒序排序并且获取每组前四

select sco.project_id, sco.create_time, sco.score_all
from devops_project_score sco
where (select count(1)
       from devops_project_score cd
       where cd.project_id = sco.project_id
         and cd.create_time > sco.create_time) < 4
order by project_id, create_time desc;

看到结果，取数成功。
那么我们来分析一下这条sql。
主查询比较简单,查询加排序，有个where条件小于4就可以了。

select sco.project_id, sco.create_time, sco.score_all
from devops_project_score sco
where condition  < 4
order by project_id, create_time desc;

那么来看看子查询,他作为一个查询条件，那么只有当查出的count值能大于4的时候这条数据才能展示，那么我们具体分析一下，可以看到主表的会传入两个参数sco.project_id、sco.create_time。

(select count(1)
       from devops_project_score cd
       where cd.project_id = sco.project_id
         and cd.create_time > sco.create_time)

我们取一组数据解释一下。首先，主表会查询到这些数据，子查询通过project_id也能匹配到这些数据，那么关键条件就在create_time上了，这块有点绕，我来逐一解释一下sql执行过程，相信大家就能很好的理解了。

sql执行步骤：

1. 当我们对第一条数据进行判断时，传入时间是’2022-05-29 17:15:39’,那么执行子查询，当前项目分组中没有时间大于这条数据的，count返回0，这时代入where条件，主表第一条数据在子表中是最大的，可以展示。
2. 当我们对第二条数据进行判断时，传入时间是’2022-05-01 11:18:31’,那么执行子查询，当前项目分组中存在时间大于这条数据的，count返回1，这时代入where条件，1小于4，主表第一条数据在子表中是排名第二的，可以展示。
3. 第三条数据排名第三，count返回2，可以展示。
4. 第四条数据排名第四，count返回3，可以展示。
5. 当我们对第五条数据进行判断时，传入时间是’2022-04-29 11:19:10’,那么执行子查询，当前项目分组中存在时间大于这条数据的，count返回4，这时代入where条件，4等于4，已经不满足等式，因此这条数据在时间倒序的比较中排名第五了，不满足小于4等式条件，不能展示。
6. 后续条件都不满足，不能展示。
   是不是有点像exist关键字，有兴趣的同学可以试试
   到这一步，我们可以取出按分组排序及每组前四😁😁😁😁

2.拆分步骤，不使用变量按分组生成排名

为了方便起见，我先把前面的结果建立一个视图v_pro_score_rank_4，语句如下

create view v_pro_score_rank_4 as
select *
from devops_project_score sco
where (select count(1)
       from devops_project_score cd
       where cd.project_id = sco.project_id
         and cd.create_time > sco.create_time) < 4
order by project_id, create_time desc;

生成排名语句:

select a.project_id,
       a.create_time,
       min(a.score_all) score_all,
       count(b.project_id) as      sortBy
from v_pro_score_rank_4 a
left join v_pro_score_rank_4 b
on a.project_id = b.project_id and a.create_time <= b.create_time
group by a.project_id, a.create_time
order by a.project_id, a.create_time desc;

sql解析:

• 这里用a表连接了b表，但是条件是不等式a.create_time <= b.create_time
• a表中的一条数据会关联多条b表数据，由此可以解析出来a表在同一分组内小于几条同组数据
• 通过group by语句可以取得b表的统计值。

到这里，排名数据统计出来了，后面可以任意连表。点赞收藏，富婆包养😏😏😏