练习6-10：多层嵌套子查询、with临时表、窗口函数

表结构回顾

6. 取得平均薪水最高的部门的部门名称

第一步：获取每个部门的平均薪水

SELECT
deptNo,
AVG(sal) AS avg_sal
FROM emp
GROUP BY deptNo;

第二步：平均薪水最高的部门可能有多个部门，故不能直接order by 后limit 1,而是应该使用窗口函数

SELECT 
  deptNo,
  avg_sal,
  rank () over (ORDER BY avg_sal DESC) AS rk 
FROM
  (SELECT 
    deptNo,
    AVG(sal) AS avg_sal 
  FROM
    emp 
  GROUP BY deptNo) t;

第三步：关联部门表，获取部门名称,然后选出rk为1的

 SELECT 
 t2.deptNo,d.dName,t2.avg_sal,t2.rk
 FROM (
	SELECT -- 对平均薪水排序
		deptNo,
		avg_sal,
		rank () over (ORDER BY avg_sal DESC) AS rk 
	FROM(
		SELECT -- 获取部门平均薪水
			deptNo,
			AVG(sal) AS avg_sal 
		FROM emp 
		GROUP BY deptNo) t) t2
JOIN dept d -- 关联部门表获取部门名称
ON t2.deptNo = d.deptNo
WHERE t2.rk = 1; -- 筛选平均薪水最高的部门们

7. 求平均薪水等级最低的部门的部门名称

第一步：求出每个部门的平均薪水

SELECT 
	deptNo,
	AVG(sal) AS avg_sal
FROM emp
GROUP BY deptNo;

第二步：将第一步获得的结果作为临时表，关联薪水等级表，获得每个部门平均薪水的等级

SELECT
	deptNo,
	avg_sal,
	grade
FROM(
	SELECT 
		deptNo,
		AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptNo) t
JOIN salgrade s
ON t.avg_sal BETWEEN s.loSal AND s.hiSal;

第三步：用rank()函数对平均薪水等级升序排序

SELECT
	deptNo,
	avg_sal,
	grade,
	rank() over (ORDER BY grade) AS rk
FROM (
	SELECT
		deptNo,
		avg_sal,
		grade
	FROM(
		SELECT 
			deptNo,
			AVG(sal) AS avg_sal
		FROM emp
		GROUP BY deptNo) t
	JOIN salgrade s
	ON t.avg_sal BETWEEN s.loSal AND s.hiSal) t2;

第四步：将第三步的结果作为临时表，关联部门表，获取部门名称，选出rk=1的

SELECT
	t3.*,
	d.dName
FROM (
	SELECT
	deptNo,
	avg_sal,
	grade,
	rank() over (ORDER BY grade) AS rk
	FROM (
		SELECT
			deptNo,
			avg_sal,
			grade
		FROM(
			SELECT 
				deptNo,
				AVG(sal) AS avg_sal
			FROM emp
			GROUP BY deptNo) t
		JOIN salgrade s
		ON t.avg_sal BETWEEN s.loSal AND s.hiSal) t2) AS t3
JOIN dept d
ON t3.deptNo = d.deptNo
WHERE rk = 1;

上面的写法嵌套了太多的子查询不太好读，可以换成下面的with写法

WITH t AS ( -- 第一步：求出每个部门的平均薪水
SELECT 
	deptNo,
	AVG(sal) AS avg_sal
FROM emp
GROUP BY deptNo
),
t2 AS ( -- 第二步：将第一步获得的结果作为临时表，关联薪水等级表，获得每个部门平均薪水的等级
SELECT
	deptNo,
	avg_sal,
	grade
FROM t
JOIN salgrade s
ON t.avg_sal BETWEEN s.loSal AND s.hiSal
),
t3 AS( -- 第三步：用rank()函数对平均薪水等级排序
SELECT
	deptNo,
	avg_sal,
	grade,
	rank() over (ORDER BY grade) AS rk
FROM t2) -- 注意最后一个临时表后不需要逗号了

SELECT -- 第四步：将第三步的结果作为临时表，关联部门表，获取部门名称，选出rk=1的
	t3.*,
	d.dName
FROM t3
JOIN dept d
ON t3.deptNo = d.deptNo
WHERE rk = 1;

8. 取得比普通员工（注：员工编号没有在mgr列出现的即为普通员工）的最高薪水还要高的经理人姓名

第一步：首先需要取得mgr列出现了哪些员工编号

SELECT
	DISTINCT mgr
FROM emp;

从第一步的结果我们可以看到出现了null值，说明有个员工没有上级，这里按题目要求也算作普通员工，我们也应该将其从mrg中排除

SELECT
	DISTINCT mgr
FROM emp 
WHERE mgr IS NOT NULL;

第二步：找出不在mgr中的所有员工的最高薪水

SELECT
	MAX(sal)
FROM emp
WHERE empNo NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL);

第三步：第二步的结果已经是所有普通员工薪水的最大值，所以其他员工中只要薪资大于这个值，就一定不是普通员工,那么就是经理，所以我们把大于这个值的员工找出来就是就好

SELECT
 empNo,
 eName
 FROM emp 
 WHERE sal > (
	SELECT
		MAX(sal)
	FROM emp
	WHERE empNo NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL));
	

9. 取得薪水最高的前五名员工

如果不考虑薪水相同，限定只取5个人即可，那么直接order by后limit 5就好了

SELECT * FROM emp ORDER BY sal DESC LIMIT 5;

如果要考虑薪水相同的作为一个名次，此时取前5名应该用dense_rank()

SELECT 
*
FROM (
	SELECT 
		*,
		dense_rank() over(ORDER BY sal DESC) AS rk
	FROM emp) t
WHERE rk <= 5;

可以看到此时结果前五名是有6个员工的,因为有两个员工并列第二名

10. 取得薪水最高的第六到第十名员工

如果不考虑薪水相同，那么直接order by后limit 5,5就好了

SELECT * FROM emp ORDER BY sal DESC LIMIT 5,5;

如果要考虑薪水相同的作为一个名次，此时应该用dense_rank()

SELECT 
*
FROM (
	SELECT 
		*,
		dense_rank() over(ORDER BY sal DESC) AS rk
	FROM emp) t
WHERE 6 <= rk AND rk <= 10;