PostgreSQL教程FG031-PG查询：子查询与嵌套查询技巧

目录大纲

Part01-基础概念与理论知识
- 1.1 子查询的概念与类型
- 1.2 子查询的执行原理
Part02-生产环境规划与建议
- 2.1 子查询的使用场景
- 2.2 子查询的性能考虑
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
- 4.1 子查询实战案例
- 4.2 嵌套查询优化
Part05-风哥经验总结与分享
- 5.1 子查询的性能优化
- 5.2 常见问题与解决方案

Part01-基础概念与理论知识

1.1 子查询的概念与类型

子查询是嵌套在其他SQL语句中的查询。PostgreSQL支持以下类型的子查询：

标量子查询：返回单个值的子查询
行子查询：返回一行数据的子查询
表子查询：返回多行数据的子查询
相关子查询：引用外部查询中列的子查询
嵌套子查询：子查询中包含其他子查询

学习交流加群风哥微信: itpux-com

1.2 子查询的执行原理

子查询的执行原理：

首先执行子查询，得到结果集
然后将子查询的结果用于外部查询
对于相关子查询，外部查询的每一行都会触发一次子查询的执行

子查询的执行方式：

独立子查询：子查询可以独立执行，不依赖外部查询
相关子查询：子查询依赖外部查询的列值

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 子查询的使用场景

子查询适合以下场景：

复杂过滤条件：当需要基于另一个查询的结果进行过滤时
数据聚合：当需要先聚合数据再进行进一步处理时
数据比较：当需要比较不同数据集时
动态值：当需要使用动态计算的值作为查询条件时
数据转换：当需要对数据进行转换后再使用时

子查询使用建议：

合理使用子查询：避免过度使用复杂的子查询
考虑性能：对于大型数据集，注意子查询的性能影响
使用适当的子查询类型：根据需求选择合适的子查询类型
优化子查询：确保子查询能够有效使用索引

风哥提示：在生产环境中，应根据实际需求和数据量合理使用子查询，避免过度复杂化查询结构。

2.2 子查询的性能考虑

子查询的性能影响因素：

子查询的复杂度：复杂的子查询会增加执行时间
子查询的执行次数：相关子查询会为外部查询的每一行执行一次
子查询的结果集大小：结果集越大，处理时间越长
索引使用情况：子查询是否能够有效使用索引
查询优化器的能力：PostgreSQL查询优化器对复杂子查询的优化能力

性能优化建议：

使用EXISTS替代IN：对于存在性检查，EXISTS通常比IN更高效
使用JOIN替代子查询：在某些情况下，JOIN可能比子查询更高效
优化相关子查询：尽量避免在子查询中引用外部查询的列
使用CTE：对于复杂的子查询，使用CTE可以提高可读性和性能
添加适当的索引：确保子查询中的条件列有索引

更多学习教程公众号风哥教程itpux_com

Part03-生产环境项目实施方案

3.1 标量子查询

标量子查询返回单个值，可以用在需要单个值的地方，如SELECT列表、WHERE子句、HAVING子句等。

— 创建测试表
CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(100),
salary DECIMAL(10, 2),
hire_date DATE
);

— 插入测试数据
INSERT INTO fgedu_employees (name, department, salary, hire_date) VALUES
(‘张三’, ‘技术部’, 10000.00, ‘2024-01-01’),
(‘李四’, ‘技术部’, 12000.00, ‘2024-02-01’),
(‘王五’, ‘市场部’, 8000.00, ‘2024-03-01’),
(‘赵六’, ‘市场部’, 9000.00, ‘2024-04-01’),
(‘钱七’, ‘财务部’, 9500.00, ‘2024-05-01’),
(‘孙八’, ‘财务部’, 11000.00, ‘2024-06-01’);

— 标量子查询：获取平均工资
SELECT
name,
salary,
(SELECT AVG(salary) FROM fgedu_employees) AS avg_salary,
salary – (SELECT AVG(salary) FROM fgedu_employees) AS salary_diff
FROM fgedu_employees;

— 标量子查询：获取部门平均工资
SELECT
name,
department,
salary,
(SELECT AVG(salary) FROM fgedu_employees e2 WHERE e2.department = e1.department) AS dept_avg_salary
FROM fgedu_employees e1;

from PostgreSQL视频:www.itpux.com

3.2 行子查询

行子查询返回一行数据，可以用在需要一行数据的地方，如比较操作。

— 行子查询：获取工资最高的员工
SELECT * FROM fgedu_employees
WHERE (salary, hire_date) = (
SELECT MAX(salary), MAX(hire_date)
FROM fgedu_employees
);

— 行子查询：获取每个部门工资最高的员工
SELECT * FROM fgedu_employees e1
WHERE (department, salary) IN (
SELECT department, MAX(salary)
FROM fgedu_employees
GROUP BY department
);

3.3 表子查询

表子查询返回多行数据，可以用在需要表的地方，如FROM子句。

— 表子查询：作为FROM子句
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary
FROM (
SELECT
department,
salary
FROM fgedu_employees
WHERE hire_date >= ‘2024-03-01’
) AS recent_hires
GROUP BY department;

— 表子查询：使用别名
WITH department_stats AS (
SELECT
department,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary
FROM fgedu_employees
GROUP BY department
)
SELECT
e.name,
e.department,
e.salary,
ds.avg_salary,
ds.max_salary,
ds.min_salary
FROM fgedu_employees e
JOIN department_stats ds ON e.department = ds.department;

学习交流加群风哥QQ113257174

3.4 相关子查询

相关子查询引用外部查询中的列，对于外部查询的每一行都会执行一次。

— 相关子查询：获取每个部门中工资高于部门平均工资的员工
SELECT
name,
department,
salary
FROM fgedu_employees e1
WHERE salary > (
SELECT AVG(salary)
FROM fgedu_employees e2
WHERE e2.department = e1.department
);

— 相关子查询：使用EXISTS
SELECT
name,
department,
salary
FROM fgedu_employees e1
WHERE EXISTS (
SELECT 1
FROM fgedu_employees e2
WHERE e2.department = e1.department AND e2.salary > e1.salary
);

— 相关子查询：使用IN
SELECT
name,
department,
salary
FROM fgedu_employees
WHERE department IN (
SELECT department
FROM fgedu_employees
WHERE salary > 10000
);

Part04-生产案例与实战讲解

4.1 子查询实战案例

4.1.1 销售数据分析

— 创建销售表
CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
product_id INTEGER,
product_name VARCHAR(100),
category VARCHAR(50),
sale_date DATE,
quantity INTEGER,
unit_price DECIMAL(10, 2),
total_amount DECIMAL(10, 2)
);

— 插入测试数据
INSERT INTO fgedu_sales (product_id, product_name, category, sale_date, quantity, unit_price, total_amount) VALUES
(1, ‘手机’, ‘电子产品’, ‘2026-04-01’, 10, 5999.00, 59990.00),
(2, ‘电脑’, ‘电子产品’, ‘2026-04-01’, 5, 9999.00, 49995.00),
(3, ‘平板’, ‘电子产品’, ‘2026-04-02’, 8, 3999.00, 31992.00),
(4, ‘耳机’, ‘电子产品’, ‘2026-04-02’, 15, 999.00, 14985.00),
(5, ‘键盘’, ‘电脑配件’, ‘2026-04-03’, 12, 1999.00, 23988.00),
(6, ‘鼠标’, ‘电脑配件’, ‘2026-04-03’, 20, 599.00, 11980.00),
(7, ‘手机’, ‘电子产品’, ‘2026-04-04’, 12, 5999.00, 71988.00),
(8, ‘电脑’, ‘电子产品’, ‘2026-04-04’, 6, 9999.00, 59994.00);

— 子查询：获取每个类别的销售总量和平均单价
SELECT
category,
SUM(quantity) AS total_quantity,
SUM(total_amount) AS total_sales,
(SELECT AVG(unit_price) FROM fgedu_sales s2 WHERE s2.category = s1.category) AS avg_unit_price
FROM fgedu_sales s1
GROUP BY category
ORDER BY total_sales DESC;

— 子查询：获取销售数量高于平均水平的产品
SELECT
product_name,
category,
SUM(quantity) AS total_quantity
FROM fgedu_sales
GROUP BY product_name, category
HAVING SUM(quantity) > (
SELECT AVG(total_quantity)
FROM (
SELECT
product_name,
SUM(quantity) AS total_quantity
FROM fgedu_sales
GROUP BY product_name
) AS product_totals
);

— 子查询：获取每天销售额最高的产品
SELECT
sale_date,
product_name,
total_amount
FROM fgedu_sales s1
WHERE total_amount = (
SELECT MAX(total_amount)
FROM fgedu_sales s2
WHERE s2.sale_date = s1.sale_date
)
ORDER BY sale_date;

4.1.2 库存管理

— 创建产品表
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10, 2)
);

— 创建库存表
CREATE TABLE fgedu_inventory (
id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES fgedu_products(id),
warehouse_id INTEGER,
quantity INTEGER,
last_updated DATE
);

— 插入测试数据
INSERT INTO fgedu_products (name, category, price) VALUES
(‘手机’, ‘电子产品’, 5999.00),
(‘电脑’, ‘电子产品’, 9999.00),
(‘平板’, ‘电子产品’, 3999.00),
(‘耳机’, ‘电子产品’, 999.00),
(‘键盘’, ‘电脑配件’, 1999.00),
(‘鼠标’, ‘电脑配件’, 599.00);

INSERT INTO fgedu_inventory (product_id, warehouse_id, quantity, last_updated) VALUES
(1, 1, 50, ‘2026-04-01’),
(1, 2, 30, ‘2026-04-01’),
(2, 1, 20, ‘2026-04-01’),
(2, 2, 15, ‘2026-04-01’),
(3, 1, 40, ‘2026-04-01’),
(3, 2, 25, ‘2026-04-01’),
(4, 1, 100, ‘2026-04-01’),
(4, 2, 80, ‘2026-04-01’),
(5, 1, 60, ‘2026-04-01’),
(5, 2, 45, ‘2026-04-01’),
(6, 1, 80, ‘2026-04-01’),
(6, 2, 65, ‘2026-04-01’);

— 子查询：获取每个产品的总库存
SELECT
p.name AS product_name,
p.category,
p.price,
(SELECT SUM(quantity) FROM fgedu_inventory i WHERE i.product_id = p.id) AS total_inventory
FROM fgedu_products p
ORDER BY total_inventory DESC;

— 子查询：获取库存不足的产品（总库存小于50）
SELECT
p.name AS product_name,
p.category,
p.price,
(SELECT SUM(quantity) FROM fgedu_inventory i WHERE i.product_id = p.id) AS total_inventory
FROM fgedu_products p
WHERE (SELECT SUM(quantity) FROM fgedu_inventory i WHERE i.product_id = p.id) < 50 ORDER BY total_inventory ASC; -- 子查询：获取每个仓库中库存最多的产品 SELECT warehouse_id, p.name AS product_name, i.quantity FROM fgedu_inventory i JOIN fgedu_products p ON i.product_id = p.id WHERE (warehouse_id, quantity) IN ( SELECT warehouse_id, MAX(quantity) FROM fgedu_inventory GROUP BY warehouse_id );

风哥提示：在使用子查询时，应注意保持查询的可读性，避免过度嵌套导致查询难以理解和维护。

4.2 嵌套查询优化

4.2.1 使用CTE优化嵌套查询

— 使用CTE替代嵌套子查询
WITH daily_sales AS (
SELECT
sale_date,
SUM(total_amount) AS daily_total
FROM fgedu_sales
GROUP BY sale_date
),
weekly_average AS (
SELECT
AVG(daily_total) AS avg_daily_sales
FROM daily_sales
)
SELECT
sale_date,
daily_total,
daily_total – (SELECT avg_daily_sales FROM weekly_average) AS deviation
FROM daily_sales
ORDER BY sale_date;

— 使用多个CTE
WITH product_totals AS (
SELECT
product_id,
product_name,
SUM(quantity) AS total_quantity,
SUM(total_amount) AS total_sales
FROM fgedu_sales
GROUP BY product_id, product_name
),
category_totals AS (
SELECT
s.category,
SUM(pt.total_sales) AS category_sales
FROM fgedu_sales s
JOIN product_totals pt ON s.product_id = pt.product_id
GROUP BY s.category
)
SELECT
pt.product_name,
s.category,
pt.total_quantity,
pt.total_sales,
ct.category_sales,
ROUND((pt.total_sales / ct.category_sales) * 100, 2) AS percentage_of_category
FROM product_totals pt
JOIN fgedu_sales s ON pt.product_id = s.product_id
JOIN category_totals ct ON s.category = ct.category
GROUP BY pt.product_name, s.category, pt.total_quantity, pt.total_sales, ct.category_sales
ORDER BY ct.category_sales DESC, pt.total_sales DESC;

4.2.2 使用JOIN替代子查询

— 使用JOIN替代子查询：获取工资高于部门平均工资的员工
SELECT
e.name,
e.department,
e.salary,
dept_avg.avg_salary
FROM fgedu_employees e
JOIN (
SELECT
department,
AVG(salary) AS avg_salary
FROM fgedu_employees
GROUP BY department
) dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_salary;

— 使用JOIN替代IN子查询：获取有销售记录的产品
SELECT
p.id,
p.name,
p.category,
p.price
FROM fgedu_products p
JOIN (
SELECT DISTINCT product_id
FROM fgedu_sales
) s ON p.id = s.product_id;