表结构回顾
1. 取得每个部门最高薪水的练习人员名称
第一步:通过关键词锁定表,最高薪水、窗口查询查询查询人员名称
,函数可见查员工表即可
第二步:通过窗口函数rank()
或者dense_rank()
按照部门分组对薪水降序排序,关联之所以用rank()
或者dense_rank()
而不是分组row_number()
,是聚合因为薪水最高的员工可能有多个
SELECT*,rank() over(PARTITION BY deptno ORDER BY sal DESC) AS rkFROM 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_salFROM empGROUP BY deptNo;
第二步:要想每个员工的薪水可以和其所在部门的平均薪水比较,就应该让他们在同一条记录中出现,练习即sal
和avg_sal
能出现在同一行,窗口查询查询查询想让不同表的函数字段出现在同一行,连接查询即可
SELECT *FROM emp eJOIN ( SELECT deptNo,关联 AVG(sal) AS avg_sal FROM emp GROUP BY deptNo) tON e.deptNo = t.deptNo;
第三步:sal
和avg_sal
出现在同一行后,筛选起来就很容易了,分组在最后加一个where
即可搞定
SELECT *FROM emp eJOIN ( SELECT deptNo, AVG(sal) AS avg_sal FROM emp GROUP BY deptNo) tON e.deptNo = t.deptNoWHERE sal >avg_sal;
3. 取得每个部门的平均薪水及其所处的等级
第一步:锁定表,薪水在员工表,聚合等级在薪水等级表
第二步:首先取得每个部门的练习编号和平均薪水
SELECT deptNo, AVG(sal) AS avg_salFROM empGROUP BY deptNo;
第三步:再次强调,想让不同表中的窗口查询查询查询字段出现在同一行,连接查询即可
,函数故关联薪水等级表,求出薪水所处的等级,注:where中能出现的条件,在连接查询时也是可以用的,如!=,>,<,between and 等,不要思维固化局限于=
SELECT t.* ,s.gradeFROM ( SELECT deptNo, AVG(sal) AS avg_sal FROM emp GROUP BY deptNo) tJOIN salgrade sON 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:获取所有员工的最高薪水,然后筛选出员工薪水等于最高薪水的的员工信息
SELECTempNo,salFROM emp WHERE emp.sal = (SELECT MAX(sal) FROM emp);
注意:该题不可以用order by后取limit 1,因为获得最高薪水的员工可能有多个,该方式只能取到一个
5. 取得平均薪水最高的部门的部门编号
第一步:首先取得每个部门的平均薪水
SELECTdeptNo,AVG(sal) AS avg_salFROM empGROUP BY deptNo;
第二步: 由于最大值可能有多个,所以不能使用order by
后limit 1
的方式,而是应该作为子查询,筛选部门平均薪水等于最大值的部门
SELECT deptNo, AVG(sal) AS avg_sal FROM empGROUP BY deptNoHAVING avg_sal = ( SELECT AVG(sal) AS avg_sal FROM emp GROUP BY deptNo ORDER BY avg_sal DESC LIMIT 1);
作者:焦点