如何快速上手SQL CTE?-cte sql

译者 | 陈峻

审校 | 孙淑娟

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>

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 alias
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;

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

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

MS SQL
USE WideWorldImporters;
GO
-- Use an external column alias
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.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;

由于列别名是在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 history
USE AdventureWorks;
GO
DECLARE @productID INT = 703;
WITH LatestProductCost AS
(
SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost
FROM 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;

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

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

MS SQL
-- Getting the before and after product standard cost change
USE AdventureWorks;
GO
DECLARE @productID INT = 711;
WITH LatestProductCost AS
(
SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCost
FROM Production.ProductCostHistory pch
WHERE pch.ProductID = @productID
ORDER BY pch.StartDate DESC
),
PreviousProductCost AS
(
SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCost
FROM Production.ProductCostHistory pch
INNER JOIN LatestProductCost lpc ON pch.ProductID = lpc.ProductID
WHERE pch.ProductID = @productID
  AND pch.StartDate < lpc.StartDate
ORDER BY pch.StartDate DESC
)
SELECT
lpc.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.ProductID
INNER JOIN Production.Product p ON lpc.ProductID = p.ProductID
WHERE lpc.ProductID = @productID;

左右滑动查看完整代码在上面的代码段中，我们可以看到两个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 NULL
SET NOEXEC ON
GO
CREATE PROCEDURE dbo.uspInsertNewProductCost
(
@productID INT,
@increase DECIMAL(3,2)
)
AS
SET NOCOUNT ON;
WITH LatestProductCost AS
(
SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost
FROM 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;
GO

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

（6）在视图中使用SQL CTE

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

MS SQL
USE WideWorldImporters;
GO
CREATE VIEW dbo.vwYearlyInvoiceTotalsPerProduct
AS
WITH 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.InvoiceID
GROUP BY YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID
)
SELECT i.InvoiceYear, i.InvoiceMonth, si.StockItemName, i.Amount
FROM InvoiceCTE i
INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID
GO

（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_cursor

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

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

MS SQL
-- British Royal family
CREATE TABLE dbo.RoyalFamily
(
ID INT NOT NULL,
Name VARCHAR(60) NOT NULL,
Father INT,
Mother INT
CONSTRAINT 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 George
WITH Ancestor(ID) AS
(
-- First anchor member returns the royal family member in question
SELECT ID
FROM dbo.RoyalFamily
WHERE ID = @id
UNION
-- Second anchor member returns the father
SELECT Father
FROM dbo.RoyalFamily
WHERE ID = @id
UNION
-- Third anchor member returns the mother
SELECT Mother
FROM dbo.RoyalFamily
WHERE ID = @id
UNION ALL
-- First recursive member returns male ancestors of the previous generation
SELECT rf.Father
FROM RoyalFamily rf
INNER JOIN Ancestor a ON rf.ID = a.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation
SELECT rf.Mother
FROM RoyalFamily rf
INNER JOIN Ancestor a ON rf.ID = a.ID
)
SELECT DISTINCT rf.ID, rf.Name, rf.Mother, rf.Father
FROM RoyalFamily rf
INNER JOIN Ancestor a ON rf.ID = a.ID
ORDER 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 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.InvoiceID
WHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013'
GROUP BY MONTH(i.InvoiceDate), il.StockItemID
),
AverageAmountPerMonth AS
(
SELECT InvoiceMonth, AVG(Amount) AS Average
FROM (WITH InvoiceAmountPerMonth
      AS
  (
SELECT i.InvoiceMonth, si.StockItemName, i.Amount
FROM InvoiceCTE i
INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID
  )
)
)
SELECT * FROM AverageAmountPerMonth;

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

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

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.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;

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

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

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

译者介绍

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