以下是一些 PostgreSQL 子查询的基本用法和示例：
1. 在 SELECT 子句中使用子查询：
-- 查询每个部门的平均工资
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- 在外部查询中使用子查询
SELECT department_name, (SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) AS avg_salary
FROM departments d;
2. 在 FROM 子句中使用子查询：
-- 使用子查询创建一个派生表
SELECT *
FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) AS department_avg;
3. 在 WHERE 子句中使用子查询：
-- 查询工资高于平均工资的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
4. 在 HAVING 子句中使用子查询：
-- 查询平均工资高于总体平均工资的部门
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
5. 子查询作为 EXISTS 条件：
-- 查询至少有一个员工的部门
SELECT department_id, department_name
FROM departments
WHERE EXISTS (SELECT 1 FROM employees WHERE employees.department_id = departments.department_id);
6. 子查询作为 IN 条件：
-- 查询在指定部门工作的员工
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'HR');
这些示例涵盖了一些常见的子查询用法，但并不是 exhaustive。
转载请注明出处：http://www.pingtaimeng.com/article/detail/14152/PostgreSQL