1.简述

总体极值是总体指定名次的特殊形式，总体指定名次又是单个排名的特殊形式。

分组极值是分组指定名次的特殊形式，分组指定名次又是分组排名的特殊形式。

总体是分组的特殊形式。

一般地，分组排名的方式具有通用性。

2.几种实现的方法

2.1.比较法

# 总体极值（求员工的最高薪水及其员工编号）
SELECT employee_id,salary
FROM employee
WHERE
	salary >= ALL(SELECT salary FROM employee)
	# 当前薪水大于等于所有薪水，即当前薪水为最高薪水
ORDER BY employee_id ASC;
# 员工不唯一时按员工编号升序

# 分组极值（求每个部门内员工的最高薪水及其部门编号和员工编号）
SELECT
	department_id,
	E.employee_id AS employee_id,
	# 员工表与部门表均存在员工编号，指定输出员工表的员工编号
	salary
FROM
	employee AS E INNER JOIN department AS D
	# 联接方式据实际要求而定，此处只保留具有部门的员工和有员工的部门（不考虑无部门的员工和无员工的部门）
	USING(employee_id)
WHERE
	salary >= ALL
	(SELECT salary 
	 FROM employee INNER JOIN department USING(employee_id)
	 WHERE department_id=D.department_id)
	 # 当前薪水大于等于所在部门的所有薪水，即当前薪水为其所在部门的最高薪水
	 # 注意相关子查询的条件
ORDER BY department_id ASC,employee_id ASC;
# 根据部门编号升序，员工不唯一时再按员工编号升序

2.2.聚合函数法

# 总体极值（求员工的最高薪水及其员工编号）
SELECT employee_id,salary
FROM employee
WHERE
	salary = (SELECT MAX(salary) FROM employee)
	# 当前薪水等于最高薪水
ORDER BY employee_id ASC;

# 分组极值（求每个部门内员工的最高薪水及其部门编号和员工编号）其一
SELECT
	department_id,
	E.employee_id AS employee_id,
	salary
FROM
	employee AS E INNER JOIN department AS D
	USING(employee_id)
WHERE
	salary =
	(SELECT MAX(salary)
	 FROM employee INNER JOIN department USING(employee_id)
	 WHERE department_id=D.department_id)
	 # 当前薪水等于其所在部门的最高薪水
	 # 相关子查询

# 分组极值（求每个部门内员工的最高薪水及其部门编号和员工编号）其二
SELECT
	department_id,
	E.employee_id AS employee_id,
	salary
FROM
	employee AS E INNER JOIN department AS D
	USING(employee_id)
WHERE
	(department_id,salary) IN
	(SELECT department_id,MAX(employee)
	 FROM employee INNER JOIN department USING(employee_id)
	 GROUP BY department_id)
	 # 当前部门与薪水 在 每个部门及其最高薪水的表内
	 # 嵌套子查询

2.3.去重分页法

# 总体指定名次（求员工的第三高薪水及其员工编号）
SELECT employee_id,salary
FROM employee
WHERE
	salary = (SELECT salary FROM employee ORDER BY salary DESC LIMIT 2,1)
	# 当前薪水等于（按薪水降序后第一行的薪水），即当前薪水等于最高薪水
	# 为什么不直接使用首行？因为可能有最高薪水对应的员工有多个
ORDER BY employee_id ASC;

# 分组指定名次（求每个部门内员工的第三高薪水及其部门编号和员工编号）
WITH T AS
(SELECT
	department_id,
	E.employee_id AS employee_id,
	salary
FROM
	employee AS E INNER JOIN department AS D
	USING(employee_id))
SELECT
	department_id,employee_id,salary
FROM T AS T1
WHERE
	salary =
	(SELECT salary FROM T
	 WHERE department_id=T1.department_id 
	 ORDER BY salary DESC LIMIT 2,1)
	 # 当前薪水等于所在部门的第三高薪水
	 # 注意相关子查询的条件
ORDER BY department_id ASC,employee_id ASC;

2.4.计数法

# 总体指定名次（求员工的第三高薪水及其员工编号）
SELECT employee_id,salary
FROM employee
WHERE
	salary=
	(SELECT 
	 	DISTINCT EE.salary
	 FROM 
	 	employee AS EE LEFT JOIN employee AS EC 
	 	ON EE.salary<=EC.salary
	 	# 联接条件为左表薪水小于等于右表
	 GROUP BY EE.salary
	 # 按左表薪水分组
	 HAVING COUNT(DISTINCT EC.salary)=3)
	 # 右表薪水的不重复计数等于3，即大于等于左表当前薪水的不重复个数有3个，也即左表当前薪水排名为第三名
	 # 必须使用不重复计数以消除某一薪水的员工有多个导致的排名错误，保证是密集排名
ORDER BY employee_id ASC;

# 分组指定名次（求每个部门内员工的第三高薪水及其部门编号和员工编号）其一
WITH T AS
(SELECT
	department_id,
	E.employee_id AS employee_id,
	salary
FROM
	employee AS E INNER JOIN department AS D
	USING(employee_id))
# 联接两表以便使用
SELECT 
	department_id,employee_id,salary
FROM T
WHERE
	salary=
	(SELECT 
	 	DISTINCT EE.salary
	 FROM 
	 	T AS TE LEFT JOIN T AS TC 
	 	ON TE.department_id=TC.department_id 
	 	AND TE.salary<=TC.salary
	 WHERE TE.department_id=T.department_id 
	 GROUP BY TE.salary
	 HAVING COUNT(DISTINCT TC.salary)=3)
	 # 相关子查询
ORDER BY department_id ASC,employee_id ASC;

# 分组指定名次（求每个部门内员工的第三高薪水及其部门编号和员工编号）其二
WITH T AS
(SELECT
	department_id,
	E.employee_id AS employee_id,
	salary
FROM
	employee AS E INNER JOIN department AS D
	USING(employee_id))
SELECT 
	department_id,employee_id,salary
FROM T
WHERE
	(department_id,salary) IN
	(SELECT 
	 	DISTINCT EE.salary
	 FROM 
	 	T AS TE LEFT JOIN T AS TC 
	 	ON TE.department_id=TC.department_id 
	 	AND TE.salary<=TC.salary
	 GROUP BY TE.department_id,TE.salary
	 HAVING COUNT(DISTINCT TC.salary)=3)
	 # 嵌套子查询
ORDER BY department_id ASC,employee_id ASC;

2.5.窗口函数法

# 总体排名（求员工的薪水排名）
SELECT
	employee_id,
	salary,
	DENSE_RANK() OVER(ORDER BY salary DESC) AS w_rank
	# 排名
FROM employee;

# 分组排名（求每个部门内员工的薪水排名）
WITH T AS
(SELECT
	department_id,
	E.employee_id AS employee_id,
	salary
FROM
	employee AS E INNER JOIN department AS D
	USING(employee_id))
SELECT
	department_id,
	employee_id,
	salary,
	DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS w_rank
	# 分组排名
FROM T

2.6.变量法

# 总体排名（求员工的薪水排名）
SELECT
	employee_id,
	salary,
	IF(@v_temp<>salary,@v_rank:=@v_rank+1,@v_rank) AS v_rank,
	# 如果上一行的薪水不等于当前行的薪水则排名增加一位，否则排名不变
	@v_temp:=salary AS v_temp
	# 先进行薪水比较再进行赋值
FROM employee,(SELECT @v_rank:=0,@v_temp:=0) AS VT
# 设置变量，分别存储排名和上一行的薪水
ORDER BY salary DESC;
# 必须根据薪水逆序

# 分组排名（求每个部门内员工的薪水排名）
WITH T AS
(SELECT
	department_id,
	E.employee_id AS employee_id,
	salary
FROM
	employee AS E INNER JOIN department AS D
	USING(employee_id))
SELECT
	department_id,
	employee_id,
	salary,
	IF(@v_class:=0<>department_id,
	   		@v_rank:=1,
	   		# 如果上一行的部门不等于当前行的部门则排名重新计算
			IF(@v_temp<>salary,@v_rank:=@v_rank+1,@v_rank)
	   		# 否则（如果上一行的薪水不等于当前行的薪水则排名增加一位，否则排名不变）
	) AS v_rank,
	@v_class:=department_id AS v_class,
	# 先进行部门比较再进行赋值
	@v_temp:=salary AS v_temp
	# 先进行薪水比较再进行赋值
FROM employee,(SELECT @v_rank:=0,@v_class:=0,@v_temp:=0) AS VT
# 设置变量，分别存储排名、上一行的部门和上一行的薪水
ORDER BY department_id ASC,salary DESC;
# 必须根据部门排序再对薪水逆序

3.方法分析

3.1.方法对比

比较法：适合求极值，对于分组极值只能采用相关子查询的方式

聚合函数法：适合求极值，对于分组极值相关子查询或嵌套子查询都可以使用

去重分页法：适合求指定名次，对于分组求指定名次只能采用相关子查询的方式

计数法：适合求指定名次（也可用于求排名），对于分组求指定名次相关子查询或嵌套子查询都可以使用

窗口函数法：适合求排名

变量法：适合求排名

3.2.方法推荐

关于求极值的方法优先选择聚合函数法

关于求指定名次的方法建议使用求排名的窗口函数法，因为去重分页法与计数法不够简洁直观

关于求排名的方法建议使用窗口函数法，因为变量法细节复杂且不易理解

如果要求不排序求排名或指定名次那只能选择计数法，计数法是唯一不需排序的方法

3.3.方法总结

极值：聚合函数法

指定名次或排名：窗口函数法

不排序求指定名次或排名：计数法

3.4.相关题目

总体极值：SQL195 查找最晚入职员工的所有信息

总体指定名次：SQL196 查找入职员工时间排名倒数第三的员工所有信息

总体排名：SQL217 对所有员工的薪水按照salary降序进行1-N的排名

分组极值（无需联接）：SQL33 找出每个学校GPA最低的同学

分组极值（需联接）：SQL206 获取每个部门中当前员工薪水最高的相关信息

分组指定名次：SQL136 每类试卷得分前3名

不排序求指定名次：SQL212 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

更多知识在专栏