PostgreSQL中的子查询

PostgreSQL中的子查询允许您将一个查询的结果作为另一个查询的一部分使用。子查询可以用在各种SQL子句中，如SELECT、FROM、WHERE、HAVING，甚至在JOIN条件中。子查询有几种类型，包括标量子查询、行子查询和表子查询。

一、WHERE子句中的基本子查询

子查询在WHERE子句中的常见用法是允许您根据另一个查询的结果过滤主查询。

假设有两个表，employees和departments。

employees表

departments表

示例：查找所有在"IT"部门工作的员工。

SELECT name
FROM employees
WHERE department_id = (
    SELECT id
    FROM departments
    WHERE department_name = 'IT'
);

二、FROM子句中的子查询

子查询也可以用在FROM子句中，本质上将子查询的结果视为临时表（通常称为派生表）。

示例：计算每个部门的平均薪资，然后找出哪些部门的平均薪资大于60,000。

SELECT department_name, avg_salary
FROM (
    SELECT d.department_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.department_name
) AS avg_salary_table
WHERE avg_salary > 60000;

三、SELECT子句中的子查询

子查询也可以直接放在SELECT子句中。当您想为主查询的每一行计算一个值或检索额外信息时，这很有用。

示例：列出每位员工的姓名和他们所在部门的名称。

SELECT name,
       (SELECT department_name
        FROM departments
        WHERE id = e.department_id) AS department_name
FROM employees e;

四、相关子查询

相关子查询是引用外部查询列的子查询。与常规子查询不同，相关子查询会针对外部查询处理的每一行执行一次。

示例：找出薪资高于所在部门平均薪资的所有员工。

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

五、JOIN子句中的子查询

子查询也可以用在JOIN子句中，有效地允许您与子查询产生的结果集进行连接。

示例：找出每位员工的部门名称，但仅限于有多于一名员工的部门。

SELECT e.name, d.department_name
FROM employees e
JOIN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 1
) AS subq ON e.department_id = subq.department_id
JOIN departments d ON e.department_id = d.id;

结论

PostgreSQL中的子查询是一个强大的特性，允许您通过将一个查询嵌套在另一个查询中来构建复杂的查询。您可以根据查询的具体需求，在WHERE、FROM、SELECT和JOIN子句中使用子查询。理解何时以及如何使用子查询可以极大地增强您的SQL查询的灵活性和表现力。