开窗函数知识点（分组排名函数）

仅mysql8.0+有

1. 常见排序（row_number(生成连续不重复的序号)；rank（并列排名，会跳号）；dense_rank（并列排名，不跳号）；precent_rank(计算百分比排名)）

非开窗函数求解过程

select

r.*，(select count（0）from (select dept_id, sale_je

from ib_sale

group by dept_id,sale_je) k

where k.dept_id=r.kept_id

and r.sale_je<=k.sale_je) pm

from ib_sale r

order by dept_id,sale_je desc;

2.lag（列）over（order by id ）as prev_num 取上一行数据

lead 取下一行数据

非开窗函数：做法自连接。

partition by =group by

例子：

WITH temp AS (
SELECT
d.name Department,
e.name Employee,
e.salary Salary,
-- 核心窗口函数
RANK() OVER(
PARTITION BY e.departmentId -- 按部门分组
ORDER BY e.salary DESC -- 组内工资降序排
) rk -- 生成排名，别名rk
FROM Employee e
JOIN Department d ON e.departmentId = d.id
)
-- 只取排名1 = 部门工资最高
SELECT Department, Employee, Salary
FROM temp
WHERE rk = 1;