如何快速上手SQL CTE?

如何快速上手SQL CTE?

译者 | 陈峻

审校 | 孙淑娟

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

1.什么是SQL CTE?

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

MS SQLWITH [(column list)]AS()

2.为何要在SQL中使用CTE？

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

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

3.如何使用SQL CTE？

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

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

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

MS SQLUSE WideWorldImporters;GO-- Use an inline column aliasWITH InvoiceCTE AS(SELECT MONTH(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) AS AmountFROM Sales.InvoiceLines ilINNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth, AmountFROM InvoiceCTEORDER BY InvoiceMonth;

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

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

MS SQLUSE WideWorldImporters;GO-- Use an external column aliasWITH InvoiceCTE(InvoiceMonth, Amount)AS(SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice)FROM Sales.InvoiceLines ilINNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth, AmountFROM InvoiceCTEORDER BY InvoiceMonth;

由于列别名是在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;GODECLARE @productID INT = 703;WITH LatestProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCostFROM Production.ProductCostHistory pchWHERE pch.ProductID = @productIDORDER 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;

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

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

MS SQL-- Getting the before and after product standard cost changeUSE AdventureWorks;GODECLARE @productID INT = 711;WITH LatestProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCostFROM Production.ProductCostHistory pchWHERE pch.ProductID = @productIDORDER BY pch.StartDate DESC),PreviousProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCostFROM Production.ProductCostHistory pchINNER 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 PreviousCostFROM LatestProductCost lpcINNER JOIN PreviousProductCost ppc ON lpc.ProductID = ppc.ProductIDINNER JOIN Production.Product p ON lpc.ProductID = p.ProductIDWHERE lpc.ProductID = @productID;

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

（4）多次引用一个SQL CTE

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

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

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

MS SQLUSE AdventureWorks;GOIF OBJECT_ID('dbo.uspInsertNewProductCost') IS NOT NULLSET NOEXEC ONGOCREATE 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 pchWHERE pch.ProductID = @productIDORDER 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;GO

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

（6）在视图中使用SQL CTE

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

MS SQLUSE WideWorldImporters;GOCREATE VIEW dbo.vwYearlyInvoiceTotalsPerProductASWITH InvoiceCTE(InvoiceYear, InvoiceMonth, StockItemID, Amount)AS(SELECT YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice)FROM Sales.InvoiceLines ilINNER 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 iINNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemIDGO

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

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

MS SQLUSE WideWorldImportersGODECLARE @invoiceMonth TINYINTDECLARE @amount MONEYDECLARE invoice_cursor CURSOR FORWITH InvoiceCTE AS(SELECT MONTH(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) AS AmountFROM Sales.InvoiceLines ilINNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth, AmountFROM InvoiceCTEORDER BY InvoiceMonthOPEN invoice_cursorFETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amountWHILE @@fetch_status = 0BEGINPRINT 'Invoice Month: ' + CAST(@invoiceMonth AS VARCHAR)PRINT 'Amount: ' + CAST(@amount AS VARCHAR)FETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amountENDCLOSE invoice_cursorDEALLOCATE invoice_cursor

（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))GOINSERT 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 IDFROM dbo.RoyalFamilyWHERE ID = @idUNION-- Second anchor member returns the fatherSELECT FatherFROM dbo.RoyalFamilyWHERE ID = @idUNION-- Third anchor member returns the motherSELECT MotherFROM dbo.RoyalFamilyWHERE ID = @idUNION ALL-- First recursive member returns male ancestors of the previous generationSELECT rf.FatherFROM RoyalFamily rfINNER JOIN Ancestor a ON rf.ID = a.IDUNION ALL-- Second recursive member returns female ancestors of the previous generationSELECT rf.MotherFROM RoyalFamily rfINNER JOIN Ancestor a ON rf.ID = a.ID)SELECT DISTINCT rf.ID, rf.Name, rf.Mother, rf.FatherFROM RoyalFamily rfINNER JOIN Ancestor a ON rf.ID = a.IDORDER BY rf.ID DESC

我们使用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 SQLWITH InvoiceCTE(InvoiceMonth, StockItemID, Amount)AS(SELECT MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice)FROM Sales.InvoiceLines ilINNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'GROUP BY MONTH(i.InvoiceDate), il.StockItemID),AverageAmountPerMonth AS(SELECT InvoiceMonth, AVG(Amount) AS AverageFROM (WITH InvoiceAmountPerMonth AS (SELECT i.InvoiceMonth, si.StockItemName, i.AmountFROM InvoiceCTE iINNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID )))SELECT * FROM AverageAmountPerMonth;

（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等工具。作为*** Management Studio（简称SSMS）的智能加载项，它能够提供SQL IntelliSense、自动化完成、重构、格式化、以及调试等功能。下面，让我们来看看它是如何与SQL CTE协同工作的：

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

接着，重命名CTE。

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

MS SQLWITH InvoiceCTE(InvoiceMonth, Amount)AS(SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice)FROM Sales.InvoiceLines ilINNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth, AmountFROM InvoiceCTEORDER BY InvoiceMonth;

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

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

译者介绍

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。