SQLite - 子查询

SQLite - 子查询和 CTE

子查询，也称为内部查询或嵌套查询，是嵌入在另一个 SQL 语句中的 SELECT 语句。子查询是执行复杂数据检索和操作的强大工具。

子查询最常用于 WHERE 子句中，根据另一个查询的结果筛选数据。它们也可以用于 SELECT、FROM、INSERT、UPDATE 和 DELETE 语句中。

子查询的规则

• 子查询必须用括号 () 括起来。
• WHERE 子句中的子查询通常应返回单个列。如果它可能返回多行，则必须与适当的运算符（如 IN）一起使用。
• ORDER BY 子句通常不允许在子查询中使用，因为外部查询控制最终排序。与 LIMIT 一起使用时是一个例外。
• 为了可读性，应避免使用深度嵌套的子查询。考虑改用 JOIN 或公共表表达式（CTE）。

SELECT 语句中的子查询

这是最常见的用例。子查询的结果充当主查询的过滤器。

示例

考虑两个表：CUSTOMERS 和 ORDERS。我们想查找所有已下订单的客户。

CUSTOMERS:
ID          NAME
----------  ----------
1           John Doe
2           Jane Smith
3           Peter Jones

ORDERS:
ORDER_ID    CUSTOMER_ID   AMOUNT
----------  -----------   ------
101         2             50.00
102         2             75.00
103         3             120.00

我们可以使用带有 IN 运算符的子查询来解决此问题：

SELECT Name FROM CUSTOMERS
WHERE ID IN (SELECT CUSTOMER_ID FROM ORDERS);

内部查询 (SELECT CUSTOMER_ID FROM ORDERS) 首先执行，返回列表 (2, 3)。外部查询随后变为 SELECT Name FROM CUSTOMERS WHERE ID IN (2, 3);，产生：

NAME
----------
Jane Smith
Peter Jones

INSERT、UPDATE 和 DELETE 中的子查询

INSERT 中的子查询

您可以使用子查询将 SELECT 语句的结果插入到另一个表中。这对于归档或创建备份很有用。

-- 将旧订单归档到 ARCHIVED_ORDERS 表中
INSERT INTO ARCHIVED_ORDERS (ORDER_ID, CUSTOMER_ID, AMOUNT)
  SELECT ORDER_ID, CUSTOMER_ID, AMOUNT
  FROM ORDERS
  WHERE ORDER_DATE < '2023-01-01';

UPDATE 中的子查询

子查询可以在 UPDATE 语句的 WHERE 子句中使用，用于识别要修改的行。

-- 为总消费超过 1000 美元的客户提供 10% 折扣
UPDATE CUSTOMERS
SET DISCOUNT_TIER = 'Gold'
WHERE ID IN (
  SELECT CUSTOMER_ID
  FROM ORDERS
  GROUP BY CUSTOMER_ID
  HAVING SUM(AMOUNT) > 1000
);

DELETE 中的子查询

同样，子查询可以识别要删除的行。

-- 删除所有未下订单的客户
DELETE FROM CUSTOMERS
WHERE ID NOT IN (SELECT CUSTOMER_ID FROM ORDERS);

现代方法：公共表表达式（CTEs）

对于复杂的查询，嵌套子查询可能变得难以阅读和维护。SQLite 支持使用 WITH 子句的公共表表达式（CTE）。CTE 允许您定义一个命名的临时结果集，您可以在主查询中引用它。这显著提高了可读性。

CTE 语法

WITH CteName AS (
  -- 定义 CTE 的子查询
  SELECT ...
)
-- 使用 CTE 的主查询
SELECT ... FROM CteName;

示例：使用 CTE 重写 UPDATE

让我们使用 CTE 重写之前的 UPDATE 示例。逻辑相同，但结构清晰得多。

-- 为高价值客户定义一个 CTE
WITH HighValueCustomers AS (
  SELECT CUSTOMER_ID
  FROM ORDERS
  GROUP BY CUSTOMER_ID
  HAVING SUM(AMOUNT) > 1000
)
-- 在主 UPDATE 语句中使用 CTE
UPDATE CUSTOMERS
SET DISCOUNT_TIER = 'Gold'
WHERE ID IN (SELECT CUSTOMER_ID FROM HighValueCustomers);

CTE 是现代 SQL 的最佳实践，使您的查询更有条理、更易读、更易于调试。