【窗口函数】RANK ()

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 部门表

id	name
1	IT
2	Sales

建表与测试数据

CREATE TABLE Department (
    id INT PRIMARY KEY COMMENT '部门编号',
    name VARCHAR(20) NOT NULL COMMENT '部门名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO Department(id, name)
VALUES
(1, 'IT'),
(2, 'Sales');

CREATE TABLE Employee (
    id INT PRIMARY KEY COMMENT '员工工号',
    name VARCHAR(20) NOT NULL COMMENT '员工姓名',
    salary INT NOT NULL COMMENT '工资',
    department_id INT COMMENT '部门编号',
    FOREIGN KEY (department_id) REFERENCES Department(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO Employee(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);

题目：基于两张表，查询每个部门工资前二高的员工，相同工资并列排名

select tem.name,d.tem.department_name,tem.salary 
from (
	select e.name as name,d.`name`as department_name,e.salary,ROW_NUMBER() over(
		PARTITION by department_id
		ORDER BY salary desc
	)as rank_id
	from Employee e
	left join Department d on d.id=e.department_id
)tem
where tem.rank_id <=2

运行结果