MySQL 子查询的详细用法指南

子查询（Subquery）是嵌套在另一个 SQL 查询中的查询，用于动态构建复杂逻辑。以下是子查询的核心知识点和实际应用方法：

一、子查询的分类

  1. 按结果类型分类：

    • 标量子查询：返回单个值（一行一列）。
    • 列子查询：返回一列值（多行一列）。
    • 行子查询：返回一行值（一行多列）。
    • 表子查询：返回多行多列的结果集。

  2. 按相关性分类：

    • 相关子查询：依赖外层查询的值，逐行执行。
    • 非相关子查询：独立于外层查询，仅执行一次。

二、子查询的核心用法

1. 标量子查询（Single-Value Subquery）
  • 作用：返回单个值，常用于比较条件。
  • 示例：查询工资高于公司平均工资的员工。 
    SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);
2. 列子查询（Column Subquery）
  • 作用：返回一列数据，常与 IN、ANY、ALL 配合使用。
  • 示例：查询在技术部工作的员工。 
    SELECT name 
FROM employees 
WHERE department IN (
    SELECT department 
    FROM departments 
    WHERE dept_name = '技术部'
);
3. 行子查询（Row Subquery）
  • 作用：返回一行数据，需与行比较符（=、<>）配合。
  • 示例：查询与张三年龄和部门相同的员工。 
    SELECT name 
FROM employees 
WHERE (age, department) = (
    SELECT age, department 
    FROM employees 
    WHERE name = '张三'
);
4. 表子查询（Table Subquery）
  • 作用：作为临时表，常用于 FROM 子句。
  • 示例：查询每个部门的平均工资，并筛选高于公司平均的部门。 
    SELECT department, avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE avg_salary > (SELECT AVG(salary) FROM employees);
5. 相关子查询（Correlated Subquery）
  • 特点：依赖外层查询的值，逐行执行。
  • 示例：查询每个部门中工资高于部门平均的员工。 
    SELECT e1.name, e1.salary, e1.department
FROM employees e1 
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

三、子查询的应用场景

  1. 在 SELECT 子句中作为计算字段

    SELECT name, salary, (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

  2. 在 FROM 子句中作为临时表

    SELECT dept_name, total_sales
FROM (
    SELECT department_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY department_id
) AS dept_sales
JOIN departments ON dept_sales.department_id = departments.id;

  3. 在 WHERE 子句中过滤数据

    SELECT product_name
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM order_details
    WHERE quantity > 10
);

  4. 在 HAVING 子句中过滤分组

    SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING emp_count > (
    SELECT AVG(emp_count)
    FROM (SELECT COUNT(*) AS emp_count FROM employees GROUP BY department) AS dept_stats
);

  5. 使用 EXISTS 检查存在性

    SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

四、子查询的性能优化

  1. 避免过度嵌套：嵌套过多会导致执行效率下降。
  2. 优先使用 JOIN：能用 JOIN 解决的问题尽量不用子查询。
  3. 索引优化：确保子查询涉及的列有索引。
  4. 限制结果集：使用 LIMIT 减少子查询返回的数据量。

五、常见错误与解决

  1. 子查询返回多行：

    -- 错误示例：标量子查询返回多行
SELECT name 
FROM employees 
WHERE salary = (SELECT salary FROM employees WHERE department = '技术部');

    解决：使用 IN 或 LIMIT 1。

  2. 忽略 NULL 值：

    -- 错误示例：NOT IN 子查询包含 NULL
SELECT name 
FROM employees 
WHERE id NOT IN (SELECT manager_id FROM departments); -- manager_id 可能为 NULL

    解决：在子查询中过滤 NULL。

六、总结

子查询类型 典型场景 示例
标量子查询 比较条件中使用单一值 WHERE salary > (SELECT AVG(...))
列子查询 使用 IN、ANY、ALL 过滤 WHERE id IN (SELECT ...)
行子查询 多列比较 WHERE (col1, col2) = (SELECT ...)
相关子查询 依赖外层查询逐行处理 WHERE salary > (SELECT AVG(...) WHERE dept = outer.dept)
EXISTS/NOT EXISTS 存在性检查 WHERE EXISTS (SELECT 1 ...)

通过灵活运用子查询，可以高效解决复杂的数据检索和分析问题。建议结合实际业务需求，多练习不同类型的子查询，并注意性能优化！

以下是基于 员工与部门的6个管理表 设计的 20道子查询练习题，涵盖标量子查询、相关子查询、多表嵌套等场景，帮助您巩固子查询的核心用法。

基础题（1-10）

1. 标量子查询（WHERE子句）

题目：查询工资高于公司平均工资的员工姓名和工资。

SELECT em_name, base_salary
FROM employees
JOIN salaries ON employees.em_id = salaries.em_id
WHERE base_salary > (SELECT AVG(base_salary) FROM salaries);

2. 列子查询（IN运算符）

题目：查询所有在“技术部”工作的员工姓名。

SELECT em_name
FROM employees
WHERE em_id IN (
    SELECT em_id 
    FROM employment 
    WHERE dept_id = (
        SELECT dept_id 
        FROM departments 
        WHERE dept_name = '技术部'
    )
);

3. 相关子查询

题目：查询每个部门中工资高于该部门平均工资的员工。

SELECT e.em_name, s.base_salary, d.dept_name
FROM employees e
JOIN salaries s ON e.em_id = s.em_id
JOIN employment emp ON e.em_id = emp.em_id
JOIN departments d ON emp.dept_id = d.dept_id
WHERE s.base_salary > (
    SELECT AVG(s2.base_salary)
    FROM salaries s2
    JOIN employment emp2 ON s2.em_id = emp2.em_id
    WHERE emp2.dept_id = emp.dept_id
);

4. EXISTS子查询

题目：查询有下属员工的部门经理姓名（manager_em_id不为空且存在下属）。

SELECT em_name
FROM employees
WHERE em_id IN (
    SELECT manager_em_id
    FROM departments
    WHERE manager_em_id IS NOT NULL
)
AND EXISTS (
    SELECT 1 
    FROM employment 
    WHERE dept_id = (
        SELECT dept_id 
        FROM departments 
        WHERE manager_em_id = employees.em_id
    )
);

5. 子查询与聚合函数（HAVING）

题目：查询部门总薪资超过该部门预算80%的部门名称。

SELECT d.dept_name
FROM departments d
JOIN (
    SELECT dept_id, SUM(base_salary) AS total_salary
    FROM employment
    JOIN salaries ON employment.em_id = salaries.em_id
    GROUP BY dept_id
) AS dept_salary ON d.dept_id = dept_salary.dept_id
WHERE total_salary > d.budget * 0.8;

6. 行子查询

题目：查询与“张三”学历和性别相同的员工。

SELECT em_name
FROM employees
WHERE (education, gender) = (
    SELECT education, gender
    FROM employees
    WHERE em_name = '张三'
);

7. 子查询更新数据

题目：将“后勤部”员工的薪资增加5%。

UPDATE salaries
SET base_salary = base_salary * 1.05
WHERE em_id IN (
    SELECT em_id 
    FROM employment 
    WHERE dept_id = (
        SELECT dept_id 
        FROM departments 
        WHERE dept_name = '后勤部'
    )
);

8. 子查询删除数据

题目：删除没有地址记录的员工。

DELETE FROM employees
WHERE em_id NOT IN (
    SELECT em_id 
    FROM addresses
);

9. 子查询与CASE语句

题目：标记薪资是否超过职位薪资上限（salary_max）。

SELECT e.em_name,
       s.base_salary,
       j.job_title,
       CASE 
           WHEN s.base_salary > j.salary_max THEN '超限'
           ELSE '正常'
       END AS salary_status
FROM salaries s
JOIN employment emp ON s.em_id = emp.em_id
JOIN jobs j ON emp.job_id = j.job_id
JOIN employees e ON s.em_id = e.em_id;

10. 子查询在FROM中生成临时表

题目：查询每个省份的员工数量及平均薪资。

SELECT a.province, 
       COUNT(*) AS emp_count,
       AVG(s.base_salary) AS avg_salary
FROM addresses a
JOIN employees e ON a.em_id = e.em_id
JOIN salaries s ON e.em_id = s.em_id
GROUP BY a.province;

进阶题（11-20）

11. 多层嵌套子查询

题目：查询薪资最高的员工所在部门的名称。

SELECT dept_name
FROM departments
WHERE dept_id = (
    SELECT dept_id
    FROM employment
    WHERE em_id = (
        SELECT em_id 
        FROM salaries 
        ORDER BY base_salary DESC 
        LIMIT 1
    )
);

12. 子查询与窗口函数

题目：查询每个部门薪资排名前3的员工（使用子查询模拟窗口函数）。

SELECT dept_name, em_name, base_salary
FROM (
    SELECT d.dept_name, e.em_name, s.base_salary,
           (SELECT COUNT(*) 
            FROM salaries s2 
            JOIN employment emp2 ON s2.em_id = emp2.em_id
            WHERE emp2.dept_id = emp.dept_id AND s2.base_salary >= s.base_salary) AS rank
    FROM salaries s
    JOIN employment emp ON s.em_id = emp.em_id
    JOIN departments d ON emp.dept_id = d.dept_id
    JOIN employees e ON s.em_id = e.em_id
) AS ranked
WHERE rank <= 3;

13. 相关子查询与聚合函数

题目：查询每个员工的最新薪资记录。

SELECT e.em_name, s.base_salary, s.effect_date
FROM salaries s
JOIN employees e ON s.em_id = e.em_id
WHERE s.effect_date = (
    SELECT MAX(effect_date)
    FROM salaries 
    WHERE em_id = s.em_id
);

14. 子查询与日期函数

题目：查询试用期已结束但未转正的员工。

SELECT e.em_name, emp.probation_end
FROM employment emp
JOIN employees e ON emp.em_id = e.em_id
WHERE emp.regularization_date IS NULL
AND emp.probation_end < CURDATE();

15. 子查询与字符串函数

题目：查询邮箱域名重复的员工（如多个员工使用@company.com）。

SELECT em_name, email
FROM employees
WHERE SUBSTRING_INDEX(email, '@', -1) IN (
    SELECT SUBSTRING_INDEX(email, '@', -1)
    FROM employees
    GROUP BY SUBSTRING_INDEX(email, '@', -1)
    HAVING COUNT(*) > 1
);

16. 子查询与数学函数

题目：查询薪资超过部门平均薪资1.5倍的员工。

SELECT e.em_name, s.base_salary, d.dept_name
FROM employees e
JOIN salaries s ON e.em_id = s.em_id
JOIN employment emp ON e.em_id = emp.em_id
JOIN departments d ON emp.dept_id = d.dept_id
WHERE s.base_salary > 1.5 * (
    SELECT AVG(s2.base_salary)
    FROM salaries s2
    JOIN employment emp2 ON s2.em_id = emp2.em_id
    WHERE emp2.dept_id = emp.dept_id
);

17. 子查询与联合查询（UNION）

题目：查询薪资高于“技术部”或“市场部”平均薪资的员工。

SELECT em_name, base_salary
FROM employees
JOIN salaries ON employees.em_id = salaries.em_id
WHERE base_salary > (
    SELECT AVG(base_salary)
    FROM salaries
    JOIN employment ON salaries.em_id = employment.em_id
    JOIN departments ON employment.dept_id = departments.dept_id
    WHERE dept_name = '技术部'
)
OR base_salary > (
    SELECT AVG(base_salary)
    FROM salaries
    JOIN employment ON salaries.em_id = employment.em_id
    JOIN departments ON employment.dept_id = departments.dept_id
    WHERE dept_name = '市场部'
);

18. 子查询与JSON函数

题目：将所有员工的姓名和薪资格式化为JSON（仅限MySQL 8.0+）。

SELECT JSON_OBJECT('name', em_name, 'salary', base_salary) AS emp_json
FROM employees
JOIN salaries ON employees.em_id = salaries.em_id;

19. 子查询与动态条件

题目：查询薪资排名前10%的员工（假设数据量较大）。

SELECT em_name, base_salary
FROM (
    SELECT em_name, base_salary,
           (SELECT COUNT(*) FROM salaries) AS total,
           (SELECT COUNT(*) FROM salaries s2 WHERE s2.base_salary >= s.base_salary) AS rank
    FROM salaries s
    JOIN employees e ON s.em_id = e.em_id
) AS ranked
WHERE (rank / total) <= 0.1;

20. 复杂业务逻辑

题目：查询部门预算与总薪资支出的比例，按比例排序。

SELECT d.dept_name, 
       d.budget,
       (SELECT SUM(base_salary) 
        FROM salaries 
        JOIN employment emp ON salaries.em_id = emp.em_id
        WHERE emp.dept_id = d.dept_id) AS total_salary,
       (total_salary / d.budget) * 100 AS budget_ratio
FROM departments d
ORDER BY budget_ratio DESC;

总结

通过这20道题，您将掌握以下子查询核心技能：

  1. 标量、列、行、表子查询：不同返回结果的应用场景。
  2. 相关子查询：依赖外层查询的动态过滤。
  3. 子查询与聚合函数：结合GROUP BY和HAVING。
  4. 子查询优化：避免性能陷阱，合理使用EXISTS和JOIN。
  5. 复杂业务场景：薪资分析、部门统计、数据清洗等。

建议结合真实数据测试每道题，逐步提升复杂查询的构建能力！

©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容