SQL查询技巧-优快云博客

题目

5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

第一种方法：平均薪水最大值的获取方式为排序，然后取第一个而得到。

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

mysql> select
    ->          deptno, avg(sal) as avgsal
    -> from
    ->          emp
    -> group by
    ->          deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：取平均薪水的最大值。

mysql> select
    ->          avg(sal) avgsal
    -> from
    ->          emp
    -> group by
    ->          deptno
    -> order by
    ->          avgsal desc
    -> limit
    ->          1;
+-------------+
| avgsal      |
+-------------+
| 2916.666667 |
+-------------+

第三步：联合第一步和第二步。

mysql> select
    ->          deptno
    -> from
    ->          emp e
    -> group by
    ->          deptno
    -> having
    ->          avg(sal) = (select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);
+--------+
| deptno |
+--------+
|     10 |
+--------+

第二种方法：平均薪水最大值的获取方式为运用分组函数max，然后取第一个而得到。

mysql> select
    ->          deptno
    -> from
    ->          emp e
    -> group by
    ->          deptno
    -> having
    ->          avg(sal) = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t);
+--------+
| deptno |
+--------+
|     10 |
+--------+

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

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

mysql> select
    ->          deptno, avg(sal) avgsal
    -> from
    ->          emp
    -> group by
    ->          deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：取得平均薪水最高的薪水值。

mysql> SELECT
    ->  avg(sal) AS avgsal
    -> FROM
    ->  emp
    -> GROUP BY
    ->  deptno
    -> ORDER BY
    ->  avgsal DESC
    -> LIMIT 1;
+-------------+
| avgsal      |
+-------------+
| 2916.666667 |
+-------------+

第三步：联合第一步和第二步。

mysql> SELECT
    ->  d.dname
    -> FROM
    ->  emp e
    -> JOIN dept d ON e.deptno = d.deptno
    -> GROUP BY
    ->  d.dname
    -> HAVING
    ->  avg(e.sal) = (
    ->          SELECT
    ->                  avg(sal) AS avgsal
    ->          FROM
    ->                  emp
    ->          GROUP BY
    ->                  deptno
    ->          ORDER BY
    ->                  avgsal DESC
    ->          LIMIT 1
    ->  );
+------------+
| dname      |
+------------+
| ACCOUNTING |
+------------+

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

第一步：求部门的平均薪水。

mysql> SELECT
    ->  deptno,
    ->  avg(sal) avgsal
    -> FROM
    ->  emp
    -> GROUP BY
    ->  deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：取部门平均薪水的等级。

mysql> SELECT
    ->  t.deptno,
    ->  s.grade
    -> FROM
    ->  salgrade s
    -> JOIN (
    ->  SELECT
    ->          deptno,
    ->          avg(sal) avgsal
    ->  FROM
    ->          emp
    ->  GROUP BY
    ->          deptno
    -> ) t ON t.avgsal BETWEEN s.losal
    -> AND hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     30 |     3 |
|     10 |     4 |
|     20 |     4 |
+--------+-------+

第三步：取得部门的平均薪水的等级，然后取最低等级的部门编号。

mysql> SELECT
    ->  deptno,
    ->  min(m.grade) AS mingrade
    -> FROM
    ->  (
    ->          SELECT
    ->                  t.deptno AS deptno,
    ->                  s.grade AS grade
    ->          FROM
    ->                  salgrade s
    ->          JOIN (
    ->                  SELECT
    ->                          deptno,
    ->                          avg(sal) avgsal
    ->                  FROM
    ->                          emp
    ->                  GROUP BY
    ->                          deptno
    ->          ) t ON t.avgsal BETWEEN s.losal
    ->          AND hisal
    ->  ) m;
+--------+----------+
| deptno | mingrade |
+--------+----------+
|     30 |        3 |
+--------+----------+

第四步：将上面的结果作为临时表与dept表进行内连接，条件为dept表的deptno和上面的deptno相等。

mysql> SELECT
    ->  d.deptno,
    ->  d.dname
    -> FROM
    ->  dept d
    -> JOIN (
    ->  SELECT
    ->          deptno,
    ->          min(m.grade) AS mingrade
    ->  FROM
    ->          (
    ->                  SELECT
    ->                          t.deptno AS deptno,
    ->                          s.grade AS grade
    ->                  FROM
    ->                          salgrade s
    ->                  JOIN (
    ->                          SELECT
    ->                                  deptno,
    ->                                  avg(sal) avgsal
    ->                          FROM
    ->                                  emp
    ->                          GROUP BY
    ->                                  deptno
    ->                  ) t ON t.avgsal BETWEEN s.losal
    ->                  AND hisal
    ->          ) m
    -> ) n ON d.deptno = n.deptno;
+--------+-------+
| deptno | dname |
+--------+-------+
|     30 | SALES |
+--------+-------+

8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名。

第一步：取得普通员工的员工编号，员工姓名，员工薪水。

mysql> SELECT
    ->  empno,
    ->  ename,
    ->  sal,
    ->  mgr
    -> FROM
    ->  emp
    -> WHERE
    ->  empno NOT IN (
    ->          SELECT DISTINCT
    ->                  mgr
    ->          FROM
    ->                  emp
    ->          WHERE
    ->                  mgr IS NOT NULL
    ->  );
+-------+--------+---------+------+
| empno | ename  | sal     | mgr  |
+-------+--------+---------+------+
|  7369 | SMITH  |  800.00 | 7902 |
|  7499 | ALLEN  | 1600.00 | 7698 |
|  7521 | WARD   | 1250.00 | 7698 |
|  7654 | MARTIN | 1250.00 | 7698 |
|  7844 | TURNER | 1500.00 | 7698 |
|  7876 | ADAMS  | 1100.00 | 7788 |
|  7900 | JAMES  |  950.00 | 7698 |
|  7934 | MILLER | 1300.00 | 7782 |
+-------+--------+---------+------+

注意：not in不会自动忽略空值(NULL)，需要手工处理，in会自动忽略空值(NULL)。

第二步：取普通员工的最高薪水。

mysql> SELECT
    ->  empno,
    ->  ename,
    ->  sal,
    ->  mgr
    -> FROM
    ->  emp
    -> WHERE
    ->  empno NOT IN (
    ->          SELECT DISTINCT
    ->                  mgr
    ->          FROM
    ->                  emp
    ->          WHERE
    ->                  mgr IS NOT NULL
    ->  )
    -> ORDER BY
    ->  sal DESC
    -> LIMIT 1;
+-------+-------+---------+------+
| empno | ename | sal     | mgr  |
+-------+-------+---------+------+
|  7499 | ALLEN | 1600.00 | 7698 |
+-------+-------+---------+------+

第三步：取比普通员工薪水最高的领导人的姓名。

mysql> SELECT
    ->  ename
    -> FROM
    ->  emp
    -> WHERE
    ->  empno IN (
    ->          SELECT DISTINCT
    ->                  mgr
    ->          FROM
    ->                  emp
    ->          WHERE
    ->                  mgr IS NOT NULL
    ->  )
    -> AND sal > (
    ->  SELECT
    ->          sal
    ->  FROM
    ->          emp
    ->  WHERE
    ->          empno NOT IN (
    ->                  SELECT DISTINCT
    ->                          mgr
    ->                  FROM
    ->                          emp
    ->                  WHERE
    ->                          mgr IS NOT NULL
    ->          )
    ->  ORDER BY
    ->          sal DESC
    ->  LIMIT 1
    -> );
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+

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

mysql> SELECT
    ->  *
    -> FROM
    ->  emp
    -> ORDER BY
    ->  sal DESC
    -> LIMIT 5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+

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

mysql> SELECT
    ->  *
    -> FROM
    ->  emp
    -> ORDER BY
    ->  sal DESC
    -> LIMIT 5,
    ->  5;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

注意：limit的用法，limit 起始位置，个数。如果省略了起始位置，则默认从最开始开始，最开始的编号为0。
11、取得最后入职的5名员工。

mysql> SELECT
    ->  *
    -> FROM
    ->  emp
    -> ORDER BY
    ->  hiredate DESC
    -> LIMIT 5;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+

mysql练习题二