MYSQL笔记_子查询-优快云博客

本文详细介绍了SQL开发中的子查询技巧，包括单行和多行子查询的区别，相关子查询与非相关子查询的区分，以及如何在WHERE、HAVING和ORDERBY等SQL语句中正确使用子查询。通过实例展示了如何避免空值问题和处理多行比较操作符的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实际开发中，我们往往用 DISTINCT 来返回不重复字段的条数，
如：（COUNT(DISTINCT ID)）
就是因为 distinct 只能返回它的目标字段，而无法返回其他字段。

A，单行子查询

1，应用：查询谁的工资比ivy高

  不推荐写法
       SELECT first_name,salary
FROM employees
WHERE salary >(
                                SELECT salary
          from employees
          WHERE first_name = 'Ivy'
)

2，称谓规范

子查询包含在（）内，放在比较条件的右侧（可读性）

a，外查询（主查询）

b，内查询（子查询）

3, 子查询的分类

        角度一：子查询返回结果的条目数——>单行子查询 vs 多行子查询


       角度二：只查询是否被执行多次——>相关子查询 VS 不（非）相关子查询

        举例：查询工资大于本部门平均工资的员工，张三需要和每个部门的平均工资比较

4，子查询不返回空值

5，非法使用子查询会返回多个结果

练习

a，查询与141号或174号员工的manager_id和department_id相同的,
其他员工的employee_id,manager_id,department_id

        SELECT employee_id,manager_id,department_id
        FROM employees
        WHERE manager_id = (
                                    SELECT manager_id
                    FROM employees
                    WHERE employee_id IN(141,174)
                           )
        AND department_id =(
                                   SELECT department_id
                    FROM employees
                    WHERE employee_id IN(141,174)
                   )
        AND employee_id != 141,employee_id != 174

b，查询最低工资大于3号办公室最低工资的部门的id和最低工资

        SELECT office_id,MIN(salary)
        FROM employees
        GROUP BY office_id
        HAVING MIN(salary) > (
                               SELECT MIN(salary)
          FROM employees
          WHERE office_id = 3
                       )

c，显示employee_id,last_name,job_title,
其中,若员工offic与jobtitle为Financial Advisor的officid相同
则job_title为1，其余则为2

        SELECT employee_id,last_name,

        CASE office_id

        WHEN (

        SELECT office_id FROM employees WHERE job_title='Financial Advisor' )

        THEN '1'
ELSE '2' END AS 'job_title'
        FROM employees

B，多行子查询

和单行比较操作符一起使用,IN 任意一个

ANY / SOME 和某一个值比较
ALL 和所有值比较

1，返回其他office的员工中,比office=3部门任一员工的工资低的员工的id，姓名，office，salary
SELECT employee_id,last_name,salary,office_id

        FROM employees
        WHERE salary < ANY(SELECT salary
                                            FROM employees
                                            WHERE office_id = 3 )
        AND office_id != 3

2，查询平均工资最低的office_id

方案一：将查询结果看作一张表，表需要起别名

SELECT office_id
       FROM employees
       GROUP BY office_id
       HAVING AVG(salary) = (
                               select MIN(avg_s)
                               FROM(
                                       SELECT AVG(salary) avg_s
                                       FROM employees
                                       GROUP BY office_id
                                       ) avg_s_t

)

方案二：最小——>小于等于所有

SELECT office_id
FROM employees
GROUP BY office_id
HAVING AVG(salary) <= ALL(
                                                SELECT AVG(salary) avg_s
                                               FROM employees
                                               GROUP BY office_id
)

3，空值问题

C，相关子查询

1，应用：查询员工中工资大于本部门平均工资的员工的last_name,salary,office_id

方式一：WHERE

SELECT last_name,salary,office_id
        FROM employees E1
        WHERE salary > ( SELECT AVG(salary)
                                        FROM employees E2
                                        WHERE office_id = E1.`office_id`
)

方式二：在FROM中声明子查询

SELECT e.last_name,e.salary,e.office_id
        FROM employees e,(
                                    SELECT AVG(salary) avg_salary
                                    FROM employees
                  GROUP BY office_id
) t_avg_salary
        WHERE salary > t_avg_salary.avg_salary
        AND e.office_id = t_avg_salary.office_id

2, 在ORDERBY里使用子查询

应用：查询id，salary，按照city排序

SELECT E.last_name,E.salary,E.office_id
FROM employees E
ORDER BY (
SELECT city
FROM offices O
WHERE E.`office_id` = O.`office_id`
)

结论

SELECT————可以写子查询
FROM—————可以写子查询
WHERE————可以写子查询
GROUP BY ——不可以写子查询
HAVING————可以写子查询
ORDER BY———可以写子查询
LIMIT —————不可以写子查询

练习

1，若employees表中employee_id与job_history表中employee_id相同的数目不小于2
输出相同id的员工的id，name，和jobid

       SELECT employee_id,last_name,job_id
       FROM employees e
       WHERE 2 <= (
                   SELECT COUNT(*)
                   FROM job_history j
                   WHERE e.employee_id = j.employee_id
                   )