CTE：使用WITH进行多步查询

实际查询往往会增长：三个 JOIN、一个 GROUP BY、一个子查询、另一个子查询……它们变得不可读。 CTE（“通用表表达式”，WITH 子句）让您给一个名称 中间步骤并像主查询中的表一样使用它。

基本语法

SQL

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

CTE 仅对它后面的查询有效：它不会生存执行。

例子

查找花费最多的 3 位客户（已交付订单的总和）：

SQL

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

用英语阅读：“首先计算每个客户的支出；然后加入客户并占据前 3 名”。如果没有 CTE，您需要一个内联 FROM 中的子查询（合法，但噪音更大）。

级联多个 CTE

可以定义多个CTE，用逗号分隔，每个CTE都可以引用到前面的：

SQL

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average 因为 average 始终具有单行：笛卡尔积只是将 total_average 附加到结果的每一行。

CTE 与子查询：何时使用哪个

CTE 在以下情况下表现出色：

在主查询中使用相同的子查询两次或更多次；
您希望在命名步骤中明确结构（自记录）；
它们是递归 CTE 的先决条件（WITH RECURSIVE，本模块的下一课）。

对于仅使用一次的步骤，内联子查询更加简洁。

尝试一下

锻炼#sql.m7.l1.e1

尝试：0加载中...

使用名为“total_spent”的 CTE，为每个客户计算已交付订单的总支出 (SUM(数量 * 单价))。然后在主查询中显示前 3 位消费者的电子邮件和总计。按总数降序排列，最多 3 个。

正在加载编辑器...

显示提示

CTE 内的总数量 * 单位价格。主要在JOIN中查询客户的id。

3 次尝试后可用的解决方案

复习练习

锻炼#sql.m7.l1.e2

尝试：0加载中...

“total_spent”CTE 与之前的练习相同。在主查询中，显示支出高于平均支出的客户（至少有一个已交付订单的所有客户）的电子邮件和总计。按总数降序排列。

正在加载编辑器...

显示提示

WHERE: SELECT AVG(total) FROM total_spent 中的标量子查询。

3 次尝试后可用的解决方案