如何快速上手SQL CTE?

译者 | 陈峻

审校 | 孙淑娟

CTE是何快公用表表达式（common table expressions）的缩写。它最初是速上手在SQL:1999规范中被引入的。作为一种标准，何快它与子查询和临时表同源。速上手

下面，何快我将通过一些简单示例，速上手向您介绍SQL CTE使用方法，何快以及如何使用工具来加快SQL CTE的速上手相关编程实践。

1.什么是何快SQL CTE? 

CTE是由SELECT查询派生出来的一个临时被命名的结果集。它存在于诸如：SELECT、速上手INSERT、何快UPDATE或MERGE等外部查询的速上手执行范围内。既然是何快临时的，那么在执行完毕后，速上手CTE就会消失。何快而且，在有限的范围内，您是无法重用CTE的。CTE不但能够以递归的亿华云计算形式实现自我引用，而且可以让用户以如下代码段的形式，使用WITH语句来创建CTE：

复制MS SQL

WITH <cte_name>[(column list)]AS(<inner query defining the CTE>)<outer query: SELECT | INSERT | UPDATE | DELETE | MERGE>1.2.3.4.5.6.7. 2.为何要在SQL中使用CTE？ 

通常，在汇总数据或计算复杂公式时，我们需要将查询分成不同的块，以使得代码简洁与易懂。而CTE就能够在此方面帮助到我们。下图展示了我们将上述CTE语句实例化的逐行分析。这段代码被分为了可读性较强的内部查询和外部查询两个部分。

使用CTE的另一个场景是当需要一个分层式列表（hierarchical list）时。对此，我将在下文，以示例的形式向您展示递归式的CTE。通常，SQL CTE可以分为递归式和非递归式两种。不过，非递归式CTE并不会替换子查询、派生表或临时表。就上面的查询示例而言，每一部分都在SQL脚本中有着自己的空间。例如，如果您在另一个查询中需要临时结果集的话，网站模板由于临时表可以在脚本中涵盖更大的范围（例如：全局范围），因此您可以在各条命令中的任何位置去引用它。当然，非递归式CTE并不适用于极快的查询需求。

3.如何使用SQL CTE？ 

下面，我们将从8个方面和您讨论如何使用SQL CTE。

（1）使用内联或外部列的别名

SQL CTE支持两种形式的列别名。下面展示了第一种--使用内联表单：

复制MS SQL

USE WideWorldImporters;

GO

-- Use an inline column aliasWITH InvoiceCTE AS(SELECT MONTH(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) AS

Amount

FROM Sales.InvoiceLines

il

INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN 1/1/2013 AND 12/31/2013GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth,

Amount

FROM

InvoiceCTE

ORDER BY InvoiceMonth;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

上述代码使用了AS关键字来定义SQL查询中的列别名。其中，InvoiceMonth和Amount都是列别名。

而针对另一种列别名的形式，我们对上述代码进行了修改，并得到了下面的外部列别名代码段：

复制MS SQL

USE WideWorldImporters;

GO

-- Use an external column aliasWITH InvoiceCTE(InvoiceMonth, Amount)AS(SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice)FROM Sales.InvoiceLines

il

INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN 1/1/2013 AND 12/31/2013GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth,

Amount

FROM

InvoiceCTE

ORDER BY InvoiceMonth;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.

由于列别名是在CTE名称之后被定义的，因此两种查询都将提供如下结果集：

（2）SELECT、INSERT、UPDATE、DELETE或MERGE

除了上面使用到的SELECT语句，您也可以使用INSERT、UPDATE、DELETE 或MERGE来开发SQL CTE实例。下面我们来看一个使用INSERT的服务器租用例子：

复制MS SQL

-- Get the latest product cost and add a 2% increase in price in product cost historyUSE AdventureWorks;

GO

DECLARE @productID INT = 703;WITH LatestProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCostFROM Production.ProductCostHistory

pch

WHERE pch.ProductID =

@productID

ORDER BY pch.StartDate DESC)INSERT INTO Production.ProductCostHistory(ProductID, StartDate, EndDate, StandardCost, ModifiedDate)SELECT

@productID

,DATEADD(d,1,lpc.EndDate),DATEADD(d,366,lpc.EndDate),(lpc.StandardCost * 0.02) + lpc.StandardCost,GETDATE()FROM LatestProductCost lpc;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.

（3）一个查询中有多个CTE

您也可以在一个查询中定义多个CTE。我们来看下面的例子：

复制MS SQL

-- Getting the before and after product standard cost changeUSE AdventureWorks;

GO

DECLARE @productID INT = 711;WITH LatestProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCostFROM Production.ProductCostHistory

pch

WHERE pch.ProductID =

@productID

ORDER BY pch.StartDate DESC),PreviousProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCostFROM Production.ProductCostHistory

pch

INNER JOIN LatestProductCost lpc ON pch.ProductID = lpc.ProductIDWHERE pch.ProductID =

@productID

AND pch.StartDate < lpc.StartDateORDER BY pch.StartDate DESC)SELECTlpc.ProductID,p.Name AS

Product

,lpc.StandardCost AS

LatestCost

,lpc.StartDate,ppc.StandardCost AS

PreviousCost

FROM

LatestProductCost lpc

INNER JOIN PreviousProductCost ppc ON lpc.ProductID = ppc.ProductIDINNER JOIN Production.Product p ON lpc.ProductID = p.ProductIDWHERE lpc.ProductID = @productID;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.

左右滑动查看完整代码在上面的代码段中，我们可以看到两个CTE，它们是用逗号分隔的，其结果集为：

（4）多次引用一个SQL CTE

为了实现多次引用一个SQL CTE，我们可以让PreviousProductCost CTE引用 LatestProductCost CTE，然后让外部查询再次引用LatestProductCost CTE。

（5）在存储过程中使用SQL CTE并将各种参数传递给它

您还可以在某个存储过程中使用SQL CTE，然后将存储过程的各个参数值传递给它。请参见如下例子：

复制MS SQL

USE AdventureWorks;

GO

IF OBJECT_ID(dbo.uspInsertNewProductCost) IS NOT NULLSET NOEXEC ON

GO

CREATE PROCEDURE dbo.uspInsertNewProductCost(@productID INT,@increase DECIMAL(3,2))ASSET NOCOUNT ON;WITH LatestProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCostFROM Production.ProductCostHistory

pch

WHERE pch.ProductID =

@productID

ORDER BY pch.StartDate DESC)INSERT INTO Production.ProductCostHistory(ProductID, StartDate, EndDate, StandardCost, ModifiedDate)SELECT

@productID

,DATEADD(d,1,lpc.EndDate),DATEADD(d,366,lpc.EndDate),(lpc.StandardCost * @increase) + lpc.StandardCost,GETDATE()FROM LatestProductCost lpc;GO1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.

正如上面的代码段所示，一个CTE用于接收两个存储过程参数，@productID和@increase。这将在ProductCostHistory表中添加一个新的行。

（6）在视图中使用SQL CTE

您还可以在视图中使用SQL CTE。请参见如下例子：

复制MS SQL

USE WideWorldImporters;

GO

CREATE VIEW dbo.vwYearlyInvoiceTotalsPerProductASWITH InvoiceCTE(InvoiceYear, InvoiceMonth, StockItemID, Amount)AS(SELECT YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice)FROM Sales.InvoiceLines

il

INNER JOIN Sales.Invoices i ON i.InvoiceID = il.InvoiceIDGROUP BY YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID)SELECT i.InvoiceYear, i.InvoiceMonth, si.StockItemName, i.AmountFROM

InvoiceCTE i

INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemIDGO1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.

（7）在指针（Cursor）中使用SQL CTE

您甚至可以将SQL CTE与指针一起使用，来循环遍历各种结果。请参见如下例子：

复制MS SQL

USE WideWorldImporters

GO

DECLARE @invoiceMonth TINYINT

DECLARE @amount MONEY

DECLARE invoice_cursor CURSOR FOR

WITH InvoiceCTE AS

(

SELECT MONTH(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) AS Amount

FROM Sales.InvoiceLines il

INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceID

WHERE i.InvoiceDate BETWEEN 1/1/2013 AND 12/31/2013

GROUP BY MONTH(i.InvoiceDate)

)

SELECT InvoiceMonth, Amount

FROM InvoiceCTE

ORDER BY InvoiceMonth

OPEN invoice_cursor

FETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amount

WHILE @@fetch_status = 0

BEGIN

PRINT Invoice Month: + CAST(@invoiceMonth AS VARCHAR)

PRINT Amount: + CAST(@amount AS VARCHAR)

FETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amount

END

CLOSE invoice_cursor

DEALLOCATE invoice_cursor1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.

（8）在递归式CTE中使用临时表

递归式CTE具有一个锚成员（anchor member）和一个递归成员。您可以使用它来查询分层的数据。例如，家谱就是一种典型的分层结构。至于CTE是使用普通表，还是临时表，其实关系并不大。请参阅下面使用临时表的示例：

复制MS SQL

-- British Royal familyCREATE TABLE dbo.RoyalFamily(ID INT NOT NULL,Name VARCHAR(60) NOT NULL,Father INT,Mother INTCONSTRAINT PK_RoyalFamily_ID PRIMARY KEY (ID))

GO

INSERT INTO dbo.RoyalFamily(ID, Name, Father, Mother)VALUES(1,Philip,NULL,NULL),(2,Queen Elizabeth II,NULL,NULL),(3,Charles,1,2),(4,Anne,2,1),(5,Andrew,2,1),(6,Edward,2,1),(7,Diana,NULL,NULL),(8,Camilla,NULL,NULL),(9,Mark Philips,NULL,NULL),(10,Timothy Laurence,NULL,NULL),(11,Sarah,NULL,NULL),(12,Sophie,NULL,NULL),(13,William,3,7),(14,Harry,3,7),(15,Peter Philips,9,4),(16,Zara Tindall,9,4),(17,Beatrice,5,11),(18,Eugenie,5,11),(19,Louise,6,12),(20,James,6,12),(21,Catherine,NULL,NULL),(22,Meghan,NULL,NULL),(23,Autumn Philips,NULL,NULL),(24,Mike Tindall,NULL,NULL),(25,Jack Brooksbank,NULL,NULL),(26,George,13,21),(27,Charlotte,13,21),(28,Louis,13,21),(29,Archie Harrison Mountbatten-Windsor,14,22),(30,Savannah,15,23),(31,Isla,15,23),(32,Mia Grace,24,16),(33,Lena,24,16);DECLARE @id INT = 26; -- Prince GeorgeWITH Ancestor(ID) AS(-- First anchor member returns the royal family member in questionSELECT

ID

FROM dbo.RoyalFamilyWHERE ID =

@id

UNION-- Second anchor member returns the fatherSELECT

Father

FROM dbo.RoyalFamilyWHERE ID =

@id

UNION-- Third anchor member returns the motherSELECT

Mother

FROM dbo.RoyalFamilyWHERE ID =

@id

UNION

ALL

-- First recursive member returns male ancestors of the previous generationSELECT rf.FatherFROM

RoyalFamily rf

INNER JOIN Ancestor a ON rf.ID = a.IDUNION

ALL

-- Second recursive member returns female ancestors of the previous generationSELECT rf.MotherFROM

RoyalFamily rf

INNER JOIN Ancestor a ON rf.ID = a.ID)SELECT DISTINCT rf.ID, rf.Name, rf.Mother, rf.FatherFROM

RoyalFamily rf

INNER JOIN Ancestor a ON rf.ID = a.IDORDER BY rf.ID DESC1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.

我们使用SQL CTE获取了英国王室的家谱，下图展示了上述查询的输出：

我们来深入分析上述查询的具体情况：

母亲列和父亲列都存放了王室成员的ID。乔治王子(ID = 26)出现在顶部。他是CTE的第一个锚定成员。他的母亲是凯瑟琳(ID = 21)，父亲是威廉王子(ID = 13)。他们是第二和第三锚成员。然后，威廉王子的父母是戴安娜王妃（ID = 7）和查尔斯王子（ID = 3）。他们和下一个节点都是CTE的递归成员中的一部分。最下面，查尔斯王子的父母是伊丽莎白女王（ID = 2）和菲利普亲王（ID = 1）。

值得注意的是：错误地编写递归式CTE，可能会导致无限的死循环。为此，您可以添加MAXRECURSION n，此处的n为循环次数。而且，您可以在WHERE子句或最后一个JOIN之后的查询末尾添加它。

4.SQL CTE的使用坑点 

下面，我们来讨论有关SQL CTE的使用注意事项：

（1）WITH子句前没有分号

如果CTE的WITH子句前面没有分号，那么在您批量运行SQL语句时，会被提示存在着语法错误。请参见如下例子：

出现此类错误的原因在于WITH子句被用于表提示等其他目的了。因此，我们只需在前面的语句中添加分号即可解决该问题。如果您使用的编辑器足够智能，那么它往往会以波浪线的形式出现在CTE的名称下方，以方便您及时发现错误消息。

（2）SQL CTE的列冲突

如果你遇到下列问题，这往往源于未命名的列所导致的CTE语法错误。

锚成员和递归成员中的列数不一致。未命名的列。重复的名称。锚成员和递归成员的列的数据类型不同。

请看如下示例：

（3）在外部查询之外重用SQL CTE名称

正如前文所说，SQL CTE是不可重用的。针对前面的例子，我们不能在下一个SQL命令中再次引用InvoiceCTE，否则就会触发错误。

如果您需要在另一个批量查询中使用临时结果集，那么请要么采用临时表，要么使用更快的多个非递归式的CTE。

（4）嵌套SQL CTE

如果SQL CTE被嵌套的话，是不会起作用的。下面的代码段示例就会导致多个语法错误：

复制MS SQL

WITH InvoiceCTE(InvoiceMonth, StockItemID, Amount)AS(SELECT MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice)FROM Sales.InvoiceLines

il

INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN 1/1/2013 AND 12/31/2013GROUP BY MONTH(i.InvoiceDate), il.StockItemID),AverageAmountPerMonth AS(SELECT InvoiceMonth, AVG(Amount) AS

Average

FROM (

WITH InvoiceAmountPerMonth

AS (SELECT i.InvoiceMonth, si.StockItemName, i.AmountFROM

InvoiceCTE i

INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID )))SELECT * FROM AverageAmountPerMonth;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.

（5）在SQL CTE中需要避免的其他方面

在递归成员中出现如下关键字：

TOP

LEFT, RIGHT, and OUTER JOIN (But INNER JOIN is allowed)

GROUP BY and HAVING

Subqueries

SELECT DISTINCT

使用scalar聚合。使用SELECT INTO、带有各种查询提示的OPTION子句、以及FOR BROWSE。不带TOP子句的ORDER BY。5.SQL CTE的专业编程技巧 

在没有智能感知（IntelliSense）的情况下，我们手动键入上述代码很可能会出错。因此，我们往往需要用到Devart的SQL Complete等工具。作为SQL Server Management Studio（简称SSMS）的智能加载项，它能够提供SQL IntelliSense、自动化完成、重构、格式化、以及调试等功能。下面，让我们来看看它是如何与SQL CTE协同工作的：

首先，在SSMS的查询窗口中，请输入cte并按下Tab键。如下代码段将为您提供一个可以填写的CTE模板。

接着，重命名CTE。

然后，编辑CTE，生成类似如下的代码段：

复制MS SQL

WITH InvoiceCTE(InvoiceMonth, Amount)AS(SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice)FROM Sales.InvoiceLines

il

INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN 1/1/2013 AND 12/31/2013GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth,

Amount

FROM

InvoiceCTE

ORDER BY InvoiceMonth;1.2.3.4.5.6.7.8.9.10.11.12.13.

SQL Complete将建议您连接的表和列，因此请利用其表格建议，并使用ij之类的片段，来进行INNER JOIN。该过程如下图所示：

最后，请使用列选择器去添加相应的列。

原文链接：https://dzone.com/articles/sql-cte-how-to-master-it-in-one-sitting-with-easy

译者介绍

陈峻 （Julian Chen），51CTO社区编辑，具有十多年的IT项目实施经验，善于对内外部资源与风险实施管控，专注传播网络与信息安全知识与经验；持续以博文、专题和译文等形式，分享前沿技术与新知；经常以线上、线下等方式，开展信息安全类培训与授课。