练习1-5：窗口函数，关联查询，子查询，分组聚合查询

表结构回顾

1. 取得每个部门最高薪水的人员名称

第一步：通过关键词锁定表，最高薪水、人员名称，可见查员工表即可
第二步：通过窗口函数rank()或者dense_rank()按照部门分组对薪水降序排序，之所以用rank()或者dense_rank()而不是row_number()，是因为薪水最高的员工可能有多个

SELECT
*,
rank() over(PARTITION BY deptno ORDER BY sal DESC) AS rk
FROM emp;

第三步：筛选出每个部门排名第一的员工姓名

SELECT 
ename 
FROM (
	SELECT
	empno,
	ename,
	rank() over(PARTITION BY deptno ORDER BY sal DESC) AS rk
	FROM emp) t 
WHERE rk = 1;

2. 哪些人的薪水在部门平均薪水之上

第一步：首先要求出每个部门的平均薪水是多少

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

第二步：要想每个员工的薪水可以和其所在部门的平均薪水比较，就应该让他们在同一条记录中出现，即sal和avg_sal能出现在同一行，想让不同表的字段出现在同一行，连接查询即可

SELECT
	*
FROM emp e
JOIN (
	SELECT
		deptNo,
		AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptNo) t
ON e.deptNo = t.deptNo;

第三步：sal和avg_sal出现在同一行后，筛选起来就很容易了,在最后加一个where即可搞定

SELECT
	*
FROM emp e
JOIN (
	SELECT
		deptNo,
		AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptNo) t
ON e.deptNo = t.deptNo
WHERE sal > avg_sal;

3. 取得每个部门的平均薪水及其所处的等级

第一步：锁定表，薪水在员工表，等级在薪水等级表
第二步：首先取得每个部门的编号和平均薪水

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

第三步：再次强调，想让不同表中的字段出现在同一行，连接查询即可，故关联薪水等级表，求出薪水所处的等级，注：where中能出现的条件，在连接查询时也是可以用的，如!=,>,<,between and 等，不要思维固化局限于=

SELECT 
t.* ,
s.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;

4. 不准用组函数（max),取得拥有所有员工的最高薪水的员工信息（使用两种解决方案）

方案1：使用窗口函数，rank()或者dense_rank(),注：最高薪水的员工可能有多个

SELECT
 empNo,
 sal,
 rk
 FROM (
	SELECT
	*,
	rank() over(ORDER BY sal DESC) AS rk
	FROM emp) t
 WHERE rk = 1;

方案2：获取所有员工的最高薪水，然后筛选出员工薪水等于最高薪水的的员工信息

SELECT
empNo,
sal
FROM emp 
WHERE emp.sal = (SELECT MAX(sal) FROM emp);

注意：该题不可以用order by后取limit 1,因为获得最高薪水的员工可能有多个，该方式只能取到一个

5. 取得平均薪水最高的部门的部门编号

第一步：首先取得每个部门的平均薪水

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

第二步: 由于最大值可能有多个，所以不能使用order by后limit 1的方式，而是应该作为子查询，筛选部门平均薪水等于最大值的部门

SELECT
	deptNo,
	AVG(sal) AS avg_sal
	FROM emp
GROUP BY deptNo
HAVING avg_sal = (
	SELECT
		AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptNo
	ORDER BY avg_sal DESC
	LIMIT 1);