mysql旧版本实现row_number

mysql8.0已经有row_number了,但是很多同学用的还是老版本,当需要用到row_number的功能时,老版本也是有解决方案的。

比如我们有一张表sync_task_log,有三个字段ds_id,state,sync_time

select ds_id,state,sync_time from sync_task_log 
order by ds_id  

看到结果可以发现,每个ds_id都有多个状态和sync_time

 

 如果我们只想要每个ds_id最新一次的状态state和sync_time怎么办?

如果mysql新版本,我们可以直接使用row_number

-- row_number(),老版本不支持
SELECT
    ROW_NUMBER() OVER(PARTITION BY ds_id ORDER BY sync_time),
    ds_id,state,sync_time
FROM
    sync_task_log

我们换个写法

SELECT
    ( @row_number := @row_number + 1 ) AS ROW_NUMBER,
    ds_id,state,sync_time
from sync_task_log a,(select @row_number := 0) b
ORDER by ds_id,sync_time desc;

查看结果,发现虽然多了一个row_number字段,但是是对全局排序的!

 

 如果我们想对ds_id进行分组排序怎么办?可以这么写!

简单解读一下,我们定义了一个变量partition_key,它初始值为0,每一行的partition_key都等于ds_id;

还定义了一个遍历row_number,它由一case when表达式计算得到,当partition_key跟ds_id相同时,row_number+1,不同时重新赋值为1

SELECT
    @row_number:=CASE
        WHEN @partition_key = s.ds_id THEN @row_number + 1 
        ELSE 1
    END AS num,
    @partition_key:=s.ds_id AS ds_id,
    s.state,
    s.sync_time 
FROM
    sync_task_log s, (SELECT @row_number:=0, @partition_key:=0) AS t
ORDER BY
   ds_id, s.sync_time desc ;

查看下结果,发现我们已经对每个ds_id进行了分组,且按照sync_time降序

 

 我们只需要在上面的结果集上再加一层筛选,即可得到我们的最终结果

select ds_id,state,sync_time from 
(SELECT
    @row_number:=CASE
        WHEN @partition_key = s.ds_id THEN @row_number + 1 
        ELSE 1
    END AS num,
    @partition_key:=s.ds_id AS ds_id,
    s.state,
    s.sync_time 
FROM
    sync_task_log s, (SELECT @row_number:=0, @partition_key:=0) AS t
ORDER BY
   ds_id, s.sync_time desc )abc
  where num=1;

查看最终结果

 

posted @ 2022-11-04 11:47  Mars.wang  阅读(1414)  评论(0编辑  收藏  举报