SQL Server使用T-SQL进阶之公用表表达式(CTE)

SQL Server是一个广泛应用的关系型数据库管理系统，T-SQL（Transact-SQL）是SQL Server的扩展语言，它包含了SQL语言的所有基本元素，还增加了一些扩展功能。CTE（Common Table Expression）是T-SQL中的一种高级特性，它是一种与视图类似的结构，用于定义可以重复使用的命名查询，通常用于复杂查询或子查询。

一、CTE的基本语法

CTE语法如下所示：

WITH CTE_Name (column1, column2, column3, …) AS
(
    SELECT …
    FROM …
    WHERE …
)
SELECT …
FROM CTE_Name

其中，CTE_Name是公用表表达式的名称，column1、column2、column3等是CTE_Name查询结果集中的列名，SELECT语句是公用表表达式的主体部分，FROM子句中引用查询的表、列、连接类型和过滤条件。公用表表达式结构中的主查询使用了WITH关键字指定CTE，在SELECT语句中可以引用CTE的结果集。公用表表达式也可以迭代地引用自身，以生成递归查询。

二、CTE的优点

使用CTE可以实现以下功能：

提高SQL查询的可读性和可维护性
简化SQL查询的逻辑
减少SQL查询的写作量

三、CTE的使用场景

CTE通常用于以下场景：

执行递归查询
在一次操作中执行多个操作
在多个查询中重用同一个结果集，减少重复SQL的写作量

下面我们将分别展示以上三个场景。

1. 执行递归查询

递归查询是一种自引用的查询方式，在一个表格中实现一个对象到对象间的查找匹配。递归查询的使用场景非常广泛，常用于组织结构、树形结构、路由等数据结构的维护和查询。

例如，下面这个例子演示如何使用公用表表达式解决相关问题：

在product库中，有一个product_categories表格，其结构及数据如下：

product_categories

id	name	parent_id
1	A	null
2	B	1
3	C	1
4	D	3
5	E	2

该表格中有一个树形结构，parent_id为null的是根节点，其余为叶节点。我们想要查询C节点下的所有叶节点。这时，可以使用递归查询。

使用CTE语法进行递归查询的基本格式如下：

WITH {CTE名称}(列名1，列名2，列名3，…… ，PathLevel) AS   --PathLevel为辅助列，CTE递归结束条件
( 
    SELECT 列名1，列名2，列名3，…… ，路径深度 = CAST(路径序列 AS VARCHAR(1000))  --设定起始条件，此为递归初始环节
    FROM 表格
    WHERE ?????               --确定起始节点
    UNION ALL   
    SELECT 子查询表格.列名1，子查询表格.列名2，子查询表格.列名3，…… ，路径深度 = CAST(通用表名称_1_2.路径深度 + '-' + CAST(子查询表格.路径序列 AS VARCHAR(1000)) AS VARCHAR(1000))  --递归核心代码，注意设置递归结束条件
    FROM {CTE名称} 通用表名称_1_2  
    JOIN 表格 子查询表格  
    ON 通用表名称_1_2.列名（子查询表格）=子查询表格.列名（表格）
    WHERE 通用表名称_1_2.PathLevel NOT LIKE CASE WHEN CHARINDEX('-', 通用表名称_1_2.PathLevel) > 0 THEN LEFT(子查询表格.路径序列, CHARINDEX('-', 子查询表格.路径序列) - 1) + '%'
    ELSE CAST(子查询表格.路径序列 AS VARCHAR(1000)) + '%'
    END    --避免环状节点，重复加入已经处理的Point
) 
SELECT * FROM {CTE名称}
WHERE ??????                 --确定查询条件

通过以上公用表表达式语法，我们可以进行如下查询，找到C节点下的所有叶节点：

WITH CTE_Tree AS 
(
    SELECT id, name, parent_id, CAST(id AS VARCHAR(1000)) PathLevel
    FROM product_categories
    WHERE name = 'C'
    UNION ALL
    SELECT pc.id, pc.name, pc.parent_id, CAST(ct.PathLevel + '-' + CAST(pc.id AS VARCHAR(10)) AS VARCHAR(1000)) PathLevel
    FROM CTE_Tree ct
    INNER JOIN product_categories pc ON ct.id = pc.parent_id
    WHERE ct.PathLevel NOT LIKE '%-' + CAST(pc.id AS VARCHAR(10))
)
SELECT id, name, parent_id FROM CTE_Tree
WHERE id NOT IN (SELECT parent_id FROM CTE_Tree WHERE parent_id IS NOT NULL)

返回结果为：

id	name	parent_id
4	D	3
5	E	2

可以看到，我们成功找到了C节点下的所有叶节点。

2. 在一次操作中执行多个操作

使用CTE可以在一次操作中执行多个操作，有效减少SQL代码的复杂度和冗余度。考虑以下场景，我们需要获取最新的5个评论，并统计出用户的一些信息，包括评论数、点赞数、头像等等。该场景需要使用多个子查询，而使用公用表表达式可以将多个子查询合并为一个查询。

例如，下面这个例子演示如何使用公用表表达式一次性获取多个由子查询组成的数据：

WITH CTE_Comments AS 
(
    SELECT TOP 5 id, content, user_id, likes, reply_count, created_at
    FROM comments
    ORDER BY created_at DESC
),
CTE_User AS
(
    SELECT id, username, avatar_url
    FROM users
),
CTE_User_Stat AS
(
    SELECT user_id, COUNT(id) AS comment_count
    FROM comments
    GROUP BY user_id
),
CTE_Summary AS
(
    SELECT c.id, c.content, c.likes, c.reply_count, c.created_at, u.username, u.avatar_url, us.comment_count
    FROM CTE_Comments c
    INNER JOIN CTE_User u ON c.user_id = u.id
    INNER JOIN CTE_User_Stat us ON c.user_id = us.user_id
)
SELECT * FROM CTE_Summary

以上代码将最新的5条评论、用户信息、用户评论数，通过一次公用表表达式的与操作，最终获得一个统计汇总的查询结果。公用表表达式使得代码逻辑更加清晰，易于维护。

3. 在多个查询中重用同一个结果集，减少重复SQL的写作量

使用公用表表达式可以在多个查询中重用相同的结果集，减少SQL代码的重复并提高查询效率。

例如，下面这个例子中，有一个order_items表格和一个items表格，其中order_items表格的每一条记录都对应着items表格中某个产品的一条记录。在order_items表格中，product_id是items表格中每一条产品记录的唯一标识。我们需要查询一个包含以下列的结果集：订单编号、物品名称、物品数量。

WITH CTE_Items AS 
(
    SELECT id, name
    FROM items
),
CTE_Order_Item AS 
(
    SELECT order_id, product_id, quantity
    FROM order_items
)
SELECT oi.order_id, i.name, oi.quantity
FROM CTE_Order_Item oi
INNER JOIN CTE_Items i ON oi.product_id = i.id

以上代码中，使用公用表表达式分别定义了items表格和order_items表格，之后的查询中均是对这两个表格的引用。这种使用公用表表达式的方法可以减少重复代码，使得代码更加简洁易懂。