SQL 查询之公用表表达式（CTE）-极客码头

CTE 的公用全称是 common table expression，即公用表表达式，表表它可以简化 SQL 查询代码。达式CTE 是公用可命名的子查询，它是表表在 SQL 查询执行期间临时创建的，包含列与记录的达式虚拟表。CTE 只能被调用它的公用主查询访问，主查询执行完毕后会被删除。表表

CTE 可以简化查询语句

CTE的达式基础语法如下。

复制1WITH my_cte AS ( 2 SELECT a,公用b,c 3 FROM T1 4) 5SELECT a,c 6FROM my_cte 7WHERE ....1.2.3.4.5.6.7.

CTE 语句以 WITH 关键字开始，所以 CTE 又被称为 WITH 语句。表表在 WITH 关键字之后是达式 CTE 的名字；在 AS 关键字之后的括号里写入要实现的查询语句。

在上例中，公用CTE 的表表名字是 my_cte，查询语句为 SELECT a,达式b,c FROM T1。

主查询在定义 CTE 语句的括号后面。定义 CTE 之后，即可在主查询中通过 CTE 的IT技术网名字对其进行引用。主查询也称为外部查询，在这个示例中是 SELECT a,c FROM my_cte WHERE ...。

简单的 CTE 示例

本文中的例子都基于以下这个 sales 表，这个表模拟了一家连锁书店的销售数据，示例如下。

示例数据

生成 sales 表的 SQL 代码如下。

复制1-- 创建名为sales的表 2CREATE TABLE sales ( 3 branch VARCHAR(50), 4 date DATE, 5 seller VARCHAR(50), 6 item VARCHAR(50), 7 quantity INT, 8 unit_price DECIMAL(10, 2) 9); 10 11-- 插入数据 12INSERT INTO sales (branch, date, seller, item, quantity, unit_price) 13VALUES 14 (Beijing-1, 2024-06-07, Zhangsan, SQL Data Analysis, 1, 80), 15 (Shanghai-1, 2024-06-06, Lisi, Python Data Analysis, 2, 120), 16 (Shanghai-2, 2024-06-07, Wangwu, Pandas Workshop, 1, 60), 17 (Beijing-1, 2024-06-07, Zhangsan, Polars in Action, 1, 50), 18 (Shanghai-2, 2024-06-07, Wangwu, Echarts Data Charts, 2, 90), 19 (Shanghai-1, 2024-06-07, Lisi, Mastering Pandas, 5, 75), 20 (Shanghai-1, 2024-06-07, Zhaoliu, R Projects, 2, 100);1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.

我们先看一个简单的例子。输出 sales 表中的原始数据记录，但要在 sales 表中再添加一列，显示当天（date）在同一分店中售出图书的最高价格。获取售价最高的图书价格可以使用如下 CTE。

复制1WITH highest AS ( 2 SELECT 3 branch, 4 date, 5 MAX(unit_price) AS highest_price 6 FROM sales 7 GROUP BY branch, date 8) 9SELECT 10 sales.*, 11 h.highest_price 12FROM sales 13JOIN highest h 14 ON sales.branch = h.branch 15 AND sales.date = h.date1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

上述查询定义了名为 highest 的 CTE，这个虚拟表包含的列为 branch、date 和highest_price，分别表示分店的名称、销售日期和当天该分店销售的最高单价。

然后，在主查询中，高防服务器像使用常规表一样使用虚拟表 highest。

最后，将 highest 的查询结果与 sales 表进行连接。

查询的结果如下。

查询结果

下面这个例子显示各分店最高的单日收入。

复制1WITH daily_revenue AS ( 2 SELECT 3 branch, 4 date, 5 SUM(unit_price * quantity) AS daily_revenue 6 FROM sales 7 WHERE EXTRACT(YEAR FROM date) = 2024 8 GROUP BY 1,2 9) 10SELECT 11 branch, 12 MAX(daily_revenue) max_daily_revenue 13FROM daily_revenue 14GROUP BY 1 15ORDER BY 2 DESC1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

在这段代码中，首先定义名为 daily_revenue 的 CTE，包含的列为 branch、date 和 daily_revenue，数据记录为每家分店在 2024 年内每一天的日销售额。

然后，在主查询中，获取 2024 年每家分店的最高日销售额。

最后，按max_daily_revenue 降序排列输出数据。

查询结果

在 SQL 高级查询中使用 CTE

主查询中可以使用多个 CTE。下面的示例中介绍如何使用多个 CTE 对复杂的 SQL 语句进行简化。

假设要显示某个城市的月销售额，以及该城市中每个分店的销售额。

在本例中，先创建两个 CTE。再在主查询中把这两个 CTE 连接起来。服务器租用

复制1WITH shanghai1_monthly_revenue AS ( 2 SELECT 3 EXTRACT(MONTH FROM date) as month, 4 SUM(unit_price * quantity) AS revenue 5 FROM sales 6 WHERE EXTRACT(YEAR FROM date) = 2024 7 AND branch = Shanghai-1 8 GROUP BY 1 9), 10shanghai2_monthly_revenue AS ( 11 SELECT 12 EXTRACT(MONTH FROM date) as month, 13 SUM(unit_price * quantity) AS revenue 14 FROM sales 15 WHERE EXTRACT(YEAR FROM date) = 2024 16 AND branch = Shanghai-2 17 GROUP BY 1 18) 19SELECT 20 s1.month, 21 s1.revenue + s2.revenue AS shanghai_revenue, 22 s1.revenue AS shanghai1_revenue, 23 s2.revenue AS shanghai2_revenue 24FROM shanghai1_monthly_revenue s1, shanghai2_monthly_revenue s2 25WHERE s1.month = s2.month1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.

在上述代码中，定义了 shanghai1_monthly_revenue 与 shanghai2_monthly_revenue 两个 CTE，使用这两个 CTE 获取 2024 年上海各分店的月销售额。

接下来，使用 month 列连接这两个 CTE，并把两个分店的销售额相加，以获取上海的总销售额。

查询结果如下。

查询结果

下例显示每家分店最大金额的订单及该订单的日期。为此，需要创建一个 CTE，根据订单金额为各分店的订单进行排名（position 列就是排名）。

复制1WITH tickets AS ( 2 SELECT distinct 3 branch, 4 date, 5 unit_price * quantity AS ticket_amount, 6 ROW_NUMBER() OVER ( 7 PARTITION BY branch 8 ORDER by unit_price * quantity DESC 9 ) AS position 10 FROM sales 11 ORDER BY 3 DESC 12) 13SELECT 14 branch, 15 date, 16 ticket_amount 17FROM tickets 18WHERE position =11.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.

在这段代码中，创建了名为 tickets 的 CTE，包含 branch、date、titcket_amount和 position。

在主查询中，过滤 position 等于 1 的记录，以获取各分店销售额最大的订单。

查询结果如下。

查询结果

SQL 查询中的嵌套 CTE

本例介绍如何使用嵌套 CTE。输出所有单价超过 90 元的图书，以及上海分店（Shanghai-2）的销售数量。

复制1WITH over_90_items AS ( 2 SELECT DISTINCT 3 item, 4 unit_price 5 FROM sales 6 WHERE unit_price >=90 7), 8shanghai2_over_90 AS ( 9 SELECT 10 o90.item, 11 o90.unit_price, 12 coalesce(SUM(s.quantity), 0) as total_sold 13 FROM over_90_items o90 14 LEFT JOIN sales s 15 ON o90.item = s.item AND s.branch = Shanghai-2 16 GROUP BY o90.item, o90.unit_price 17) 18SELECT item, unit_price, total_sold 19FROM shanghai2_over_90;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.

over_90_items 用于筛选单价大于或等于 90 元的图书。

shanghai2_over_90 用于筛选 Shanghai-2 分店售出的超过 90 元的图书的数量。

这就是嵌套 CTE 的基本用法。

注意，shanghai2_over_90 中的 FROM 子句中引用了 over_90_items。

使用 LEFT JOIN sales 的原因是 Shanghai-2 分店可能没有销售过价格超过 90 元的图书。

查询结果如下。

查询结果

递归查询与 CTE

在关系型数据库中，表示数据层级结构的表很常见，例如，上下级关系、父子组件关系等。

为了在这些层级结构中进行从上到下或从下到上的遍历，可以使用递归 CTE。

RECURSIVE 是定义遍历递归数据结构的 CTE 的关键字。CTE 递归查询的形式如下。

复制1WITH RECURSIVE cte_name AS ( 2 CTE_query_definition -- 非递归查询项 3UNION ALL 4 CTE_query_definition -- 递归查询项 5) 6SELECT * FROM cte_name;1.2.3.4.5.6.

限于本文的篇幅，有 CTE 的递归查询就先不赘述了，关注我们的公众号，我会在后续的文章中进行介绍。

结语

SQL 的 CTE 非常强大，它可以让 SQL 查询代码更简洁，逻辑更清晰。