应用-极值与排名的实现

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;
# 必须根据部门排序再对薪水逆序