SQL进阶 - Common Table Expression(CTE) in SQL Server

悦享骑诚 2021-06-02

2524

“优雅而简洁的SQL语言，就像面试穿正装，能够给人留下非常好的第一印象”

本文主要介绍基于SQL Server的CTE的用法，包含了CTE的定义，优点和缺点，以及正确的使用方法，供各位参考。

希望DWH的Engineer们能够学以致用，让你的代码更加优雅。

所有代码基于SQL Server的标准始例数据库：AdventureWorks2019，如果您想尝试本文的代码请先下载并在SQL Server上还原此数据库备份文件，超链接如下所示，请复制并粘贴到浏览器中下载。
https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak

—

CTE 简介

Common Table Expression，伴随SQL Server 2005版本发布，是一种临时的，虚拟的数据结果集，可自定义表及字段名称，可以在SELECT，INSERT，Update，DELETE或MERGE语句中使用，也可以用于视图VIEW或存储过程PROCEDURE中。

使用CTE，对比子查询：

能够让SQL代码更加具有可读性，容易让人理解其前后逻辑，便于维护
对于特定的具有层级结构（Hierarchy）的数据，能够通过递归实现高效的数据查询
通过递归可以实现数值的循环计算

目前共有两类CTE表达式：

非递归CTE表达式

单一CTE表达式
多CTE表达式

递归CTE表达式

我们先从非递归CTE表达式开始入手。

01
—
非递归CTE表达式

CTE的标准语法为：

;WITH expression_name [(column_name[,...n])]
AS
( CTE_query_definition )
SELECT <column_list>
FROM expression_name;

非递归单一CTE表达式

查询员工ID，Title

-------------- Type One: Non-Recursive Single CTE---------


USE AdventureWorks2019;
GO


;WITH Employee_CTE (BusinessEntityID, Title)
 AS (SELECT [BusinessEntityID],
            [JobTitle]
     FROM HumanResources.Employee)
SELECT Employee_CTE.BusinessEntityID,
       Employee_CTE.Title
FROM Employee_CTE;

Step 1，如下图蓝色部分，定义CTE表达式，如CTE名称，字段，查询语句；
Step 2，如下图黄色部分，执行查询语句；

执行结果：

所以非递归CTE使用起来基本上和View视图是类似的，当然上面的CTE你也可以用在视图中进行灵活使用，通过Join操作来获得更多的信息，如：

-------------- Type One: Non-Recursive CTE In VIEW--------


CREATE VIEW [dbo].[Employee_View_CTE]
AS
WITH Employee_CTE (BusinessEntityID, Title)
AS (
   SELECT [BusinessEntityID],
          [JobTitle]
   FROM HumanResources.Employee)
SELECT ECTE.[BusinessEntityID],
       ECTE.[Title],
       P.[FirstName],
       P.[MiddleName],
       P.[LastName]
FROM Employee_CTE AS ECTE
    INNER JOIN Person.[Person] AS P
        ON P.[BusinessEntityID] = ECTE.[BusinessEntityID];
‍

这样，通过CTE + View就能够实现较为复杂的逻辑了，并且可读性较高。但是，这个方案的弊端会无形中增加数据库对象的数量，如果逻辑需要临时调整，仍然需要修改视图，不够灵活，因此便有了下面的方法。

非递归多CTE表达式

-------------- Type One: Non-Recursive Multiple CTE-------


;WITH Employee_CTE (BusinessEntityID, Title)
AS (SELECT [BusinessEntityID],
           [JobTitle]
    FROM HumanResources.[Employee]),
     Employee_Name_CTE (BusinessEntityID, Title, FirstName, MiddleName, LastName)
AS (SELECT ECTE.[BusinessEntityID],
           ECTE.[Title],
           P.[FirstName],
           P.[MiddleName],
           P.[LastName]
    FROM Employee_CTE AS ECTE
        INNER JOIN Person.[Person] AS P
            ON P.[BusinessEntityID] = ECTE.[BusinessEntityID])
SELECT [BusinessEntityID],
       [Title],
       [FirstName],
       [MiddleName],
       [LastName]
FROM Employee_Name_CTE;

注意点：

多CTE表达式需要通过逗号分隔；
在下一级CTE表达式中，引用上一级CTE表达式名称
通过最后一级CTE名称进行查询。

执行结果：

多CTE的好处显而易见，灵活，可读性高，随查随用，不浪费数据库空间。

建议大家将一些比较复杂的连接和子查询语句通过多CTE实现，增加可读性和易维护性；

—

递归CTE - Recursive CTE

递归CTE，相对于非递归CTE，可以理解为在CTE定义语句中引用自身，来达到递归调用的目的。

示例1，假设我们要生成一个数据集合，只有一列“n”，数值从数字1 ~ 50。可以通过下面的语句生成。

-------------- Type Two: Recursive CTE--------------------


DECLARE @Cnt INT = 50;    -- 定义整型变量Cnt


;WITH CTE_Cnt (n)         -- 定义CTE
AS (SELECT 1
    UNION ALL
    SELECT n + 1
    FROM CTE_Cnt          -- 递归调用CTE本身
    WHERE n < @Cnt)       -- 设置递归结束条件
SELECT n
FROM CTE_Cnt
OPTION (MAXRECURSION 0);  -- 设置最大调用次数，默认100，0代表无限制

Step1：定义Anchor成员，即CTE名称和列名
Step2：定义递归成员，并引用CTE自身
Step3：设置递归结束条件
Step4：执行查询语句，并设置递归调用最大执行次数可选项

执行结果（仅截取前10行记录）：

示例2，通过递归CTE取得斐波那契数列集合。

引用百度百科回忆其含义：数列从第3项开始，每一项都等于前两项之和。

斐波那契数列（Fibonacci sequence），又称黄金分割数列，因数学家莱昂纳多·斐波那契（Leonardoda Fibonacci）以兔子繁殖为例子而引入，故又称为“兔子数列”，指的是这样一个数列：0、1、1、2、3、5、8、13、21、34、……在数学上，斐波那契数列以如下被以递推的方法定义：F(0)=0，F(1)=1, F(n)=F(n - 1)+F(n - 2)（n ≥ 2，n ∈ N*）在现代物理、准晶体结构、化学等领域，斐波纳契数列都有直接的应用，为此，美国数学会从 1963 年起出版了以《斐波纳契数列季刊》为名的一份数学杂志，用于专门刊载这方面的研究成果。 [1]
百度百科

------ Type Two: Recursive CTE - Fibonacci Sequence------


;WITH Fibonacci (PrevN, N)             -- 定义CTE名称
AS (SELECT 0,
           1                           -- 定义斐波那契数列前两列
    UNION ALL
    SELECT N,
           PrevN + N
    FROM Fibonacci                     -- 引用CTE自身
    WHERE N < 100000000)               -- 设置斐波那契数列结束条件
SELECT STRING_AGG(PrevN, ',') AS Fibo  -- 执行查询语句
FROM Fibonacci                         -- STRING_AGG适用于SQLServer2017之后的版本
OPTION (MAXRECURSION 0);               -- 设置最大递归次数可选项

执行结果：

示例3，统计具有树形层级结构的数据集合。

AdventureWorks2019数据库中有两张表，其中：

BillOfMaterials，是物料表，包含产品中使用的物料信息，包含BOM的层级Level，产品ID，产品零部件ID等信息。

Product，是产品主数据表，包含了产品的主数据信息，如产品ID，产品名称等。

我们希望通过一个查询语句得到目前物料所使用到的产品，零部件ID，名称及层级关系情况。

可以通过下面的递归CTE语句实现：

------ Type Two: Recursive CTE - Hierarchy Data------


;WITH cte_BOM (ProductID, Name, ProductLevel, ProductAssemblyID)
AS (SELECT P.[ProductID],
           CAST(P.[Name] AS VARCHAR(100)),
           1,
           NULL
    FROM Production.Product AS P
        INNER JOIN Production.BillOfMaterials AS BOM
            ON BOM.[ComponentID] = P.[ProductID]
               AND BOM.[ProductAssemblyID] IS NULL
               AND
               (
                   BOM.[EndDate] IS NULL
                   OR BOM.[EndDate] > GETDATE()
               )
    UNION ALL
    SELECT P.[ProductID],
           CAST(cte_BOM.[Name] + '\' + P.[Name] AS VARCHAR(100)),
           cte_BOM.[ProductLevel] + 1,
           cte_BOM.[ProductID]
    FROM cte_BOM
        INNER JOIN Production.BillOfMaterials AS BOM
            ON BOM.[ProductAssemblyID] = cte_BOM.[ProductID]
        INNER JOIN Production.Product AS P
            ON BOM.[ComponentID] = P.[ProductID]
               AND
               (
                   BOM.EndDate IS NULL
                   OR BOM.EndDate > GETDATE()
               ))
SELECT [ProductID],
       [Name],
       [ProductLevel],
       [ProductAssemblyID]
FROM cte_BOM
ORDER BY [Name];

执行结果：

—

总结

对于SQL语法的灵活掌握，能够最大限度的提升自己处理数据的能力。

本文中介绍的CTE语句属于进阶的SQL语法，是ANSI SQL 99标准的一部分，灵活掌握之后，能够提高SQL代码的可读性，执行性能及可维护性。对于特殊的数据集合，如数学计算，树形层级结构数据的计算统计有比较好的适用场景。

sql server

文章转载自悦享骑诚，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL进阶 - Common Table Expression(CTE) in SQL Server

评论