数据库|查询语句 后浪云PostgreSQL教程PostgreSQL 子查询详解与应用技巧

数据库|查询语句 后浪云PostgreSQL教程：PostgreSQL 子查询详解与应用技巧

场景引入：电商数据分析的烦恼

"小王，帮我查一下上个月销售额超过平均值的店铺有哪些？"产品经理老张急匆匆地走过来。

小王盯着电脑屏幕，手指在键盘上犹豫不决，他知道要先计算所有店铺的平均销售额，然后再筛选出超过这个值的店铺,但如何在一个查询里完成这两步操作呢？

"别着急，用PostgreSQL的子查询就能轻松搞定！"路过的技术主管小李看出了小王的困惑。

什么是子查询？

子查询就是嵌套在其他SQL查询中的查询，它像一个查询中的"小助手"，先执行内部查询获取结果，然后将结果传递给外部查询使用，PostgreSQL的子查询功能非常强大,能帮你解决许多复杂的数据检索问题。

基础子查询语法

让我们从小王遇到的问题开始,看看如何使用子查询：

SELECT shop_name, sales_amount
FROM shops
WHERE sales_amount > (SELECT AVG(sales_amount) FROM shops)
AND sales_month = '2025-07';

这个查询先计算所有店铺的平均销售额（内部查询），然后筛选出销售额超过这个平均值的店铺（外部查询）。

子查询的几种常见类型

WHERE子句中的子查询

这是最常见的子查询用法，就像上面的例子，再来看一个例子，找出比"旗舰店"销售额更高的店铺：

SELECT shop_name, sales_amount
FROM shops
WHERE sales_amount > (SELECT sales_amount FROM shops WHERE shop_name = '旗舰店');

FROM子句中的子查询（派生表）

有时候我们需要把一个查询结果当作临时表来使用：

SELECT a.shop_name, a.sales_amount, b.avg_sales
FROM shops a,
     (SELECT AVG(sales_amount) as avg_sales FROM shops) b
WHERE a.sales_amount > b.avg_sales;

SELECT子句中的子查询（标量子查询）

这种子查询必须返回单个值,可以用于计算列：

SELECT 
    shop_name, 
    sales_amount,
    sales_amount - (SELECT AVG(sales_amount) FROM shops) as diff_from_avg
FROM shops;

带IN的子查询

查找有订单的所有客户：

SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

高级子查询技巧

EXISTS和NOT EXISTS

当只需要判断子查询是否返回结果，而不关心具体内容时,使用EXISTS更高效：

-- 查找有订单的客户
SELECT customer_name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

相关子查询

子查询引用了外部查询的列,这种子查询会对外部查询的每一行执行一次：

-- 查找每个品类中价格高于该品类平均价格的产品
SELECT p1.product_name, p1.price, p1.category
FROM products p1
WHERE p1.price > (SELECT AVG(p2.price) 
                  FROM products p2 
                  WHERE p2.category = p1.category);

使用WITH子句的公共表表达式(CTE)

对于复杂的子查询,使用WITH可以大大提高可读性：

WITH monthly_avg AS (
    SELECT AVG(sales_amount) as avg_sales FROM shops WHERE sales_month = '2025-07'
),
top_shops AS (
    SELECT shop_name, sales_amount FROM shops 
    WHERE sales_amount > (SELECT avg_sales FROM monthly_avg)
)
SELECT * FROM top_shops ORDER BY sales_amount DESC;

性能优化建议

1. 避免过度嵌套：多层嵌套子查询会影响性能，考虑使用JOIN或CTE重构
2. 使用EXISTS代替IN：当子查询结果集大时，EXISTS通常更高效
3. 限制子查询返回的列：只选择必要的列，减少数据处理量
4. 为子查询中使用的列建立索引：特别是WHERE和JOIN条件中的列

实际应用案例

案例1：找出每个部门工资最高的员工

SELECT e.employee_name, e.salary, e.department
FROM employees e
WHERE e.salary = (SELECT MAX(salary) 
                  FROM employees 
                  WHERE department = e.department);

案例2：查找从未下过订单的产品

SELECT product_name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);

案例3：计算累计销售额

SELECT 
    order_date,
    amount,
    (SELECT SUM(amount) 
     FROM orders o2 
     WHERE o2.order_date <= o1.order_date) as running_total
FROM orders o1
ORDER BY order_date;

常见错误与陷阱

1. 单行子查询返回多行：使用比较运算符(=, >, <等)时，子查询必须返回单行

   -- 错误示例
   SELECT name FROM products WHERE price = (SELECT price FROM products WHERE category = '电子产品');

   修正方法：使用IN或修改子查询确保返回单行

   

2. 忽略NULL值的影响：任何与NULL的比较结果都是UNKNOWN

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

   如果某些员工salary为NULL，他们不会被包含在结果中

3. 性能问题：相关子查询可能对外部查询的每一行执行一次，大数据集时性能差

PostgreSQL的子查询功能强大而灵活，是处理复杂数据检索需求的利器，从简单的单层子查询到多层的相关子查询，再到使用WITH子句的CTE,掌握这些技巧能让你在数据分析工作中游刃有余。

记住小王的故事吗？他现在已经能轻松应对各种数据查询需求了，下次当你面对复杂的数据问题时，不妨想想："这个问题能用子查询解决吗？"

实践是学习的最好方式，打开你的PostgreSQL客户端，尝试用子查询解决你工作中的实际问题吧！遇到性能问题时，再回头看看我们的优化建议，相信你会不断进步,成为查询高手。