RANK() 窗口函数（并列排名，跳名次）
函数说明
| 函数 | 作用 | 特点 |
|---|
| RANK() | 分组排名 | 相同值同名次，后续名次跳过，结果：1,1,3,4,4,6 |
实战案例：各部门工资前二的员工
数据表
Employee 员工表
| id | name | salary | department_id |
|---|
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | San | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
Department 部门表
建表与测试数据
CREATETABLEDepartment(idINTPRIMARYKEYCOMMENT'部门编号',nameVARCHAR(20)NOTNULLCOMMENT'部门名称')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;INSERTINTODepartment(id,name)VALUES(1,'IT'),(2,'Sales');CREATETABLEEmployee(idINTPRIMARYKEYCOMMENT'员工工号',nameVARCHAR(20)NOTNULLCOMMENT'员工姓名',salaryINTNOTNULLCOMMENT'工资',department_idINTCOMMENT'部门编号',FOREIGNKEY(department_id)REFERENCESDepartment(id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;INSERTINTOEmployee(id,name,salary,department_id)VALUES(1,'Joe',85000,1),(2,'Henry',80000,2),(3,'San',60000,2),(4,'Max',90000,1),(5,'Janet',69000,1),(6,'Randy',85000,1),(7,'Will',70000,1);
题目：基于两张表，查询每个部门工资前二高的员工，相同工资并列排名
selecttem.name,d.tem.department_name,tem.salaryfrom(selecte.nameasname,d.`name`asdepartment_name,e.salary,ROW_NUMBER()over(PARTITIONbydepartment_idORDERBYsalarydesc)asrank_idfromEmployee eleftjoinDepartment dond.id=e.department_id)temwheretem.rank_id<=2
运行结果
![]()
