SQL 查询之公用表表达式(CTE)

数据库 其他数据库
在关系型数据库中,表示数据层级结构的表很常见,例如,上下级关系、父子组件关系等。为了在这些层级结构中进行从上到下或从下到上的遍历,可以使用递归 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 ....

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 查询代码更简洁,逻辑更清晰。

责任编辑:武晓燕 来源: Python学研大本营
相关推荐

2010-11-03 11:55:23

DB2公共表表达式

2009-09-10 15:35:07

LINQ查询表达式

2011-10-28 16:34:13

LINQ

2010-10-21 10:56:29

SQL Server查

2009-08-27 09:13:28

LINQ查询表达式

2009-09-07 17:18:33

LINQ查询表达式

2009-09-11 09:53:16

Linq查询表达式

2009-03-23 10:47:43

数据库SQLLINQ

2009-09-17 11:08:55

LINQ查询表达式

2017-05-12 10:47:45

Linux正则表达式程序基础

2019-12-17 17:01:02

MySQLSQL数据库

2024-03-01 08:51:01

Django查询表达式查询语句

2009-09-17 09:09:50

Lambda表达式Linq查询

2009-06-08 16:49:05

Java正则表达式group

2010-11-12 13:20:31

SQL Server

2024-10-17 17:03:43

SQL正则表达式MySQL

2024-10-11 18:36:51

2014-01-05 17:41:09

PostgreSQL表达式

2011-08-12 10:31:01

T-SQL查询基于列的逻辑表达式

2022-01-14 07:56:39

C#动态查询
点赞
收藏

51CTO技术栈公众号