CTE 可以简化查询语句
CTE的基础语法如下。
1WITH my_cte AS (
2 SELECT a,b,c
3 FROM T1
4)
5SELECT a,c
6FROM my_cte
7WHERE ....CTE 语句以 WITH 关键字开始，所以 CTE 又被称为 WITH 语句。在 WITH 关键字之后是 CTE 的名字；在 AS 关键字之后的括号里写入要实现的查询语句。
在上例中，CTE 的名字是 my_cte，查询语句为 SELECT a,b,c FROM T1。
主查询在定义 CTE 语句的括号后面。定义 CTE 之后，即可在主查询中通过 CTE 的名字对其进行引用。主查询也称为外部查询，在这个示例中是 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);我们先看一个简单的例子。输出 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.date上述查询定义了名为 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 DESC在这段代码中，首先定义名为 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.month在上述代码中，定义了 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 =1在这段代码中，创建了名为 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;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;限于本文的篇幅，有 CTE 的递归查询就先不赘述了，关注我们的公众号，我会在后续的文章中进行介绍。
结语
SQL 的 CTE 非常强大，它可以让 SQL 查询代码更简洁，逻辑更清晰。
