Subquery子查询怎么写

在 SQL 中，子查询（Subquery）是指嵌套在另一个 SQL 语句中的查询，通常出现在 SELECT、FROM、WHERE、HAVING 等子句中。下面按常见用法 + 示例给你系统讲一下。

一、WHERE 中的子查询（最常见）

1️⃣ 子查询返回单个值（标量子查询）

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

✅ 子查询只返回一个值

2️⃣ 子查询返回多个值（IN / NOT IN）

SELECT *
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Beijing'
);

SELECT *
FROM employees
WHERE department_id NOT IN (
    SELECT department_id
    FROM departments
    WHERE status = 'closed'
);

3️⃣ 子查询返回多行（ANY / ALL）

-- 工资高于任意一个销售
SELECT *
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE job = 'Sales'
);

-- 工资高于所有销售
SELECT *
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE job = 'Sales'
);

4️⃣ EXISTS 子查询（存在判断，性能通常更好）

SELECT *
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.id
);

二、SELECT 中的子查询（标量子查询）

SELECT 
    name,
    (SELECT COUNT(*)
     FROM orders o
     WHERE o.customer_id = c.id) AS order_count
FROM customers c;

⚠️ 只能返回一行一列

三、FROM 中的子查询（派生表）

SELECT dept_id, avg_salary
FROM (
    SELECT department_id AS dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) t
WHERE avg_salary > 10000;

✅ 子查询必须起别名

四、HAVING 中的子查询

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
);

五、关联子查询 vs 非关联子查询

✅ 非关联子查询

先执行子查询，再执行外层

WHERE salary > (SELECT AVG(salary) FROM employees)

✅ 关联子查询

子查询依赖外层字段（逐行执行）

SELECT e.name
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

六、常见错误 ❌

❌ 子查询返回多列

WHERE id = (SELECT id, name FROM t) -- 错误

❌ IN 子查询包含 NULL

WHERE id NOT IN (SELECT id FROM t) -- 若含 NULL，结果可能为空

如果你愿意，可以告诉我：

• 你用的是 MySQL / Oracle / SQL Server / PostgreSQL
• 具体业务场景（比如“查每个部门工资最高的员工”）

我可以直接帮你写✅