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

仅mysql8.0有常见排序row_number(生成连续不重复的序号)rank并列排名会跳号dense_rank并列排名不跳号precent_rank(计算百分比排名)非开窗函数求解过程selectr.*(select count0from (select dept_id, sale_jefrom ib_salegroup by dept_id,sale_je) kwhere k.dept_idr.kept_idand r.sale_jek.sale_je) pmfrom ib_sale rorder by dept_id,sale_je desc;2.lag列overorder by id as prev_num 取上一行数据lead 取下一行数据非开窗函数做法自连接。partition by group by例子WITH temp AS (SELECTd.name Department,e.name Employee,e.salary Salary,-- 核心窗口函数RANK() OVER(PARTITION BY e.departmentId -- 按部门分组ORDER BY e.salary DESC -- 组内工资降序排) rk -- 生成排名别名rkFROM Employee eJOIN Department d ON e.departmentId d.id)-- 只取排名1 部门工资最高SELECT Department, Employee, SalaryFROM tempWHERE rk 1;