mysql8.0多窗口函数分区排序的问题
先上结论:
窗口函数分区后,其他字段按照最后一个窗口函数的规定顺序进行分区排序。上一个计算后的结果也会因为最后一个窗口函数的规则而改变!!!!!!!!!!!!!!!!
----------------------------先看一段sql,今天做题的时候忽然发现这个问题。
select id,
month,
salary,
row_number() over (partition by id order by salary) as `num1`,
row_number() over (partition by month order by id) as `num2`
from employee;
这个sql里面有两个不同的分区排序窗口函数。那结果会怎样呢!
先看一下全表
-- 全表
select *
from employee;
再看一下单独窗口函数的结果
-- 按id分再按salary排序
select id, month,salary, row_number() over (partition by id order by salary) as `num1`
from employee;
再看一下两一个窗口函数的结果
-- 按month分再按id排序
select id, month,salary, row_number() over (partition by month order by id) as `num2`
from employee;
再看一下两个窗口函数的运行结果
-- 将两者结合
select id,
month,
salary,
row_number() over (partition by id order by salary) as `num1`,
row_number() over (partition by month order by id) as `num2`
from employee;
结论
窗口函数分区后,其他字段按照最后一个窗口函数的规定顺序进行分区排序。上一个计算后的结果也会因为最后一个窗口函数的规则而改变!!!!!!!!!!!!!!!!