MySQL实战--基于emp dept salgrade表

--emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | NULL       |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | NULL       | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | NULL       | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-02-04 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | NULL       | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | NULL       | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-09-06 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | NULL       | 3000.00 |    NULL |     20 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-08-09 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | NULL       | 1250.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7566 | 1981-03-12 | 1250.00 |    NULL |     30 |
|  7902 | FROD   | ANALYST   | 7782 | 1981-03-12 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7698 | NULL       | 1300.00 |    NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | NULL       | 5000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

--dept表
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)
--salgrade表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

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

--思路：
1、第一步先找出每个部门最大工资数
2、将1中的表当成临时表t,与emp e表进行连接。连接条件是：t.deptno=e.deptno and t.maxsal=e.sal

SELECT e.ename,e.sal,e.deptno FROM emp e JOIN
(select deptno,max(sal) as maxsal from emp group by deptno) t
ON e.deptno=t.deptno AND e.sal=t.maxsal;
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| FROD  | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
+-------+---------+--------+
4 rows in set (0.00 sec)

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

--思路
1、找出部门平均薪水（按照部门编号分组求平均值）
2、将查询结果当成临时表t,t表与emp e进行表连接 条件：t.deptno=e.deptno and e.sal>t.avgsal

SELECT e.ename,e.sal,t.* FROM emp e JOIN
(select deptno,avg(sal) as avgsal from emp group by deptno) t
ON e.deptno=t.deptno  AND e.sal>t.avgsal;

+-------+---------+--------+-------------+
| ename | sal     | deptno | avgsal      |
+-------+---------+--------+-------------+
| JONES | 2975.00 |     20 | 2205.000000 |
| BLAKE | 2850.00 |     30 | 1616.666667 |
| SCOTT | 3000.00 |     20 | 2205.000000 |
| FROD  | 3000.00 |     20 | 2205.000000 |
| KING  | 5000.00 |     10 | 2916.666667 |
+-------+---------+--------+-------------+
5 rows in set (0.00 sec)

3、取得部门中（所有人的）平均的薪水等级

--将所有人的薪水等级求出来再平均
--将emp e和salgrade s进行表连接，连接条件是e.sal BETWEEN s.losal AND s.hisal。

SELECT e.deptno,avg(s.grade) FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal
GROUP BY e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       3.0000 |
|     30 |       2.6667 |
+--------+--------------+
3 rows in set (0.00 sec)

变形：取得部门中（所有人的）平均的 薪水等级。（没有的）

--先求各部门平均薪水，再求对应的等级
--从emp e中取出deptno,avg（sal）并将其看作临时表t
--将t和salgrade s进行表连接，连接条件是t.avgsal BETWEEN s.losal AND s.hisal
SELECT t.*,s.grade FROM salgrade s JOIN
(SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) t
ON t.avgsal BETWEEN s.losal AND s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |