MySQL5.7窗口(开窗)函数实现

一. MySQL8.0使用窗口函数

MySQL8.0 新增了开窗函数功能，同其他 SQL 语言类似。

比如现在有一个 courses 表，现在要查询每天相同年龄的人中分数最高的那个人，直接用开窗函数。

with courses as (
    select 1 as uid, '2023-07-21' as dt, 10 as age, 80 as score
    union all
    select 2 as uid, '2023-07-21' as dt, 10 as age, 88 as score
    union all
    select 3 as uid, '2023-07-22' as dt, 12 as age, 99 as score
    union all
    select 4 as uid, '2023-07-22' as dt, 12 as age, 90 as score
)

select uid, dt, age, score
from
(
    select uid, dt, age, score
        , row_number() over(partition by dt,age order by score desc) rk
    from courses
)x
where rk=1

结果

二. MySQL5.7实现窗口函数

MySQL8.0 以前没有窗口函数，但是我们可以自己实现类似的功能。同样是现在要查询每天相同年龄的人中分数最高的那个人，涉及到两个知识点。

• @xxx  表示一个变量名称
• :=  表示给变量赋值的赋值符号

看具体实现：

with courses as (
    select 1 as uid, '2023-07-21' as dt, 10 as age, 80 as score
    union all
    select 2 as uid, '2023-07-21' as dt, 10 as age, 88 as score
    union all
    select 3 as uid, '2023-07-22' as dt, 12 as age, 99 as score
    union all
    select 4 as uid, '2023-07-22' as dt, 12 as age, 90 as score
)

select uid, dt, age, score
from
(
    select if(@dt = t1.dt and @age = t1.age, @curRank := @curRank + 1, @curRank := 1) as rk
         , @dt := t1.dt                                                               as dt
         , @age := t1.age                                                             as age
         , uid
         , score
    from (select uid, dt, age, score from courses order by dt, age, score desc) t1,
         (select @dt := null, @age := null, @curRank := 0) t2
)x
where rk=1

执行流程解释：

• 临时表 t2 的作用是初始化 3 个变量 @dt @age @curRank
• t1 表 按照 dt, age, score desc 排序，到时候按照这个顺序进行 +1 排名
• t1 表排好序的数据，一行一行的读取，首先执行 if(xxx, @curRank := @curRank + 1, @curRank := 1) ，由于 @dt我们初始化的是 null ，所以和 t1.dt 是不相等的，此时 @curRank 为 1，age 也是同理。
• @dt := t1.dt 赋值，age 也是同理
• 第一行处理完后 @dt 和 @age 都有值了，if 判断条件 如果是同一组的话，就用 +1 逻辑了
• 同理把所有的数据遍历完就完成排名了