PostgreSQL 子查询详解

在 PostgreSQL 里，子查询指的是嵌套于其他查询里的查询。子查询能够用于生成中间结果，进而在主查询里使用。下面会从子查询的类型、使用场景以及示例等方面展开详细介绍。

子查询的类型

• 标量子查询：返回单一的值，可用于WHERE、SELECT等子句。
• 行子查询：返回一行数据，可在WHERE子句中使用。
• 列子查询：返回一列数据，可在WHERE子句里借助IN、ANY、ALL等操作符运用。
• 表子查询：返回一个表，可在FROM子句中使用。

子查询的使用场景

• 过滤数据：借助子查询得到满足特定条件的数据，然后在主查询中使用这些数据进行过滤。
• 计算聚合值：在子查询中计算聚合值，再在主查询里使用这些聚合值。
• 连接多个表：在子查询中连接多个表，之后在主查询里使用子查询的结果。

示例

假设存在两个表：orders（订单表）和customers（客户表）。

 

-- 创建 customers 表
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- 创建 orders 表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 插入示例数据
INSERT INTO customers (customer_name) VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO orders (customer_id, order_amount) VALUES (1, 100.00), (1, 200.00), (2, 150.00);

 

标量子查询

下面的查询使用标量子查询来获取客户Alice的订单总金额：

 

SELECT 
    (SELECT SUM(order_amount) FROM orders WHERE customer_id = (SELECT customer_id FROM customers WHERE customer_name = 'Alice')) AS total_amount;

 

列子查询

下面的查询使用列子查询来获取所有有订单的客户姓名：

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

 

表子查询

下面的查询使用表子查询来获取每个客户的订单总金额：

 

SELECT 
    c.customer_name, 
    subquery.total_amount
FROM 
    customers c
JOIN 
    (SELECT customer_id, SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id) subquery
ON 
    c.customer_id = subquery.customer_id;

 

注意事项

• 性能问题：子查询可能会影响性能，特别是在处理大量数据时。在某些情况下，可以使用连接（JOIN）来替代子查询以提高性能。
• 嵌套深度：避免过深的子查询嵌套，这会让查询变得难以理解和维护。

通过上述示例，你应该对 PostgreSQL 子查询有了更深入的了解。子查询是一种强大的工具，可以帮助你处理复杂的查询需求。