MySQL学习-多行子查询

圆之蓝调
149 阅读3分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第12天，点击查看活动详情

前言

上篇我们学习了MySQL子查询中的单行子查询。有兴趣的小伙伴可以阅读(# MySQL学习-单行子查询）。
下面继续学习MySQL子查询中的多行子查询。

多行子查询

  • 多行子查询也称为集合比较子查询。
  • 内查询返回多行。
  • 使用多行比较操作符。

多行比较操作符

操作符含义
IN等于列表中的任意一个
ANY需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME实际上是ANY的别名，作用相同，一般常使用ANY

举例一

查询各部门最低工资的员工信息。
这里需先查出各部门的最低工资，因为有不同的部门，所以子查询的结果是多行，这里使用IN。

SELECT employee_id, name
FROM employees
WHERE salary IN (
                SELECT MIN(salary)
                FROM employees
                GROUP BY department_id
                );

多行子查询，这里使用IN，就可以查出符合条件的员工信息。

举例二

返回其他job_id中比job_id为'IT'部门任一工资低的员工的员工号、姓名、job_id以及salary。

SELECT employee_id, name, job_id, salary
FROM employees
WHERE job_id <> 'IT'
AND salary < ANY (
                SELECT salary
                FROM employees
                WHERE job_id = 'IT'
                );
employee_idnamejob_idsalary
2xiaolanHR3200.00

这里使用ANY，只要其他部门中比IT部门任一值低就可以。

举例三

返回其他job_id中比job_id为'IT'部门所有工资低的员工的员工号、姓名、job_id以及salary。

分析：这里任一改成了所有，需要使用ALL操作符。

SELECT employee_id, name, job_id, salary
FROM employees
WHERE job_id <> 'IT'
AND salary < ALL (
                SELECT salary
                FROM employees
                WHERE job_id = 'IT'
                );
employee_idnamejob_idsalary
2xiaolanHR3200.00

举例四

查询平均工资最低的部门id。

分析：首先查询各个部门的平均工资，对部门进行分组。再从平均工资里找最低工资，我们先用AVG平均之后再套用MIN函数的方式试一试。

SELECT MIN(AVG(salary))
FROM employees
GROUP BY department_id

运行发现报错，这是因为MySQL聚合函数外不能再嵌套聚合函数。

结论： MySQL中聚合函数是不能嵌套使用的。

方式1

分析： 不是嵌套聚合函数的话，我们可以把平均工资的结果当成一张新表，然后在新表中查询最低工资。所以正确的写法是。

SELECT MIN(avg_sal)
FROM (
        SELECT AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
      ) t_dept_avg_sal;

这样就可以查出最低的工资，然后再查出最低工资所在的部门。

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
                        SELECT MIN(avg_sal)
                        FROM (
                                SELECT AVG(salary) avg_sal
                                FROM employees
                                GROUP BY department_id
                              ) t_dept_avg_sal
                        );
department_id
2

这样就可以查出结果。

方式2

先查出平均工资，然后找到部门平均工资是最小的值。使用<= ALL的方法，比所有值都小，自然是平均工资中的最小值。

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
                        SELECT AVG(salary) avg_sal
                        FROM employees
                        GROUP BY department_id
                        );
department_id
2

空值问题

SELECT name
FROM employees
WHERE employee_id NOT IN (
                        SELECT manager_id
                        FROM employees
                        );

从SQL语句中可以看出，想查询不是管理者的员工信息，但是运行发现没有结果。实际上不是所有的人都是管理者。为什么没有结果查出来呢？这是以为子查询的结果包含NULL值，所以NOT IN来比较的话，没有结果。这里需要过滤NULL值的情况。正确写法：

SELECT name
FROM employees
WHERE employee_id NOT IN (
                        SELECT manager_id
                        FROM employees
                        WHERE manager_id IS NOT NULL
                        );

这样就可以查出想要的结果。

今天先学习到这里，明天继续。

avatar
文章
阅读
粉丝