CTE 的全称是 common table expression,即公用表表达式,它可以简化 SQL 查询代码。CTE 是可命名的子查询,它是在 SQL 查询执行期间临时创建的,包含列与记录的虚拟表。CTE 只能被调用它的主查询访问,主查询执行完毕后会被删除。
CTE 可以简化查询语句
CTE的基础语法如下。
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 代码如下。
我们先看一个简单的例子。输出 sales 表中的原始数据记录,但要在 sales 表中再添加一列,显示当天(date)在同一分店中售出图书的最高价格。获取售价最高的图书价格可以使用如下 CTE。
上述查询定义了名为 highest 的 CTE,这个虚拟表包含的列为 branch、date 和highest_price,分别表示分店的名称、销售日期和当天该分店销售的最高单价。
然后,在主查询中,像使用常规表一样使用虚拟表 highest。
最后,将 highest 的查询结果与 sales 表进行连接。
查询的结果如下。
查询结果
下面这个例子显示各分店最高的单日收入。
在这段代码中,首先定义名为 daily_revenue 的 CTE,包含的列为 branch、date 和 daily_revenue,数据记录为每家分店在 2024 年内每一天的日销售额。
然后,在主查询中,获取 2024 年每家分店的最高日销售额。
最后,按max_daily_revenue 降序排列输出数据。
查询结果
在 SQL 高级查询中使用 CTE
主查询中可以使用多个 CTE。下面的示例中介绍如何使用多个 CTE 对复杂的 SQL 语句进行简化。
假设要显示某个城市的月销售额,以及该城市中每个分店的销售额。
在本例中,先创建两个 CTE。再在主查询中把这两个 CTE 连接起来。
在上述代码中,定义了 shanghai1_monthly_revenue 与 shanghai2_monthly_revenue 两个 CTE,使用这两个 CTE 获取 2024 年上海各分店的月销售额。
接下来,使用 month 列连接这两个 CTE,并把两个分店的销售额相加,以获取上海的总销售额。
查询结果如下。
查询结果
下例显示每家分店最大金额的订单及该订单的日期。为此,需要创建一个 CTE,根据订单金额为各分店的订单进行排名(position 列就是排名)。
在这段代码中,创建了名为 tickets 的 CTE,包含 branch、date、titcket_amount和 position。
在主查询中,过滤 position 等于 1 的记录,以获取各分店销售额最大的订单。
查询结果如下。
查询结果
SQL 查询中的嵌套 CTE
本例介绍如何使用嵌套 CTE。输出所有单价超过 90 元的图书,以及上海分店(Shanghai-2)的销售数量。
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 递归查询的形式如下。
限于本文的篇幅,有 CTE 的递归查询就先不赘述了,关注我们的公众号,我会在后续的文章中进行介绍。
结语
SQL 的 CTE 非常强大,它可以让 SQL 查询代码更简洁,逻辑更清晰。