SQL CTE：如何通过简单的例子一举掌握它

SQL CTE是另一种表达带有临时结果集的查询的方式。这个标准提高了代码的可读性。通过简单的例子来学习其中的内涵和外延。

SQL CTE有什么大问题？

CTE是普通表表达式的简称。首先，它是在SQL:1999规范中首次引入的。所以，它是相当标准的。此外，子查询和临时表也是它的近亲。

但是，什么是SQL CTE？以及如何使用它？

这篇文章将帮助你解决这个问题。伴随着简单的例子，你可以在短时间内掌握它。今天学习这个是一个无痛的经历。

但还有更多。

有一个工具，你可以用它来大幅度地加快你的编码速度。嗯，并不是说SQL CTE 很难编码。但是，这个工具就在身边，可以把事情踢开。

那么，为什么不现在就开始呢？让我们开始吧!

什么是SQL CTE？

让我们通过告诉你它是什么和它不是什么来描述它。

它是什么

首先，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>

为了说明这一点，请看下面的SQL CTE的解剖图。

那么，你为什么要在SQL中使用CTE？

当总结数据或计算一个复杂的公式时，把你的查询分成几块总是好的。

为什么？

它简化了你的代码。这使得它更容易阅读和理解。而CTE就能为你做到这一点。看到上面的例子了吗？它把工作分成了两部分：内部查询和外部查询。一个有1个CTE的内部查询也是最简单的。

所以，简而言之，CTE可以帮助你的代码块更具有可读性。

使用CTE的另一个原因是当你需要一个分层的列表时。一个递归的CTE 可以帮助你做到这一点。你会在后面的章节中看到这个例子。

由于上述原因，你可以说SQL CTE可以是递归或非递归的。

它不是什么

现在，让我们来揭开关于CTE的神秘面纱。因为你可能已经听到了关于它的传闻。

首先，一个非递归的CTE并不能取代子查询、派生表或临时表。如果你注意到前面的例子，其目的与这些查询标准相似。但是在你的SQL脚本中，每一个都有它的位置。例如，如果你在另一个查询中需要临时结果集，临时表可能是一个更好的选择。因为临时表在你的脚本中具有更大的范围。你可以在一系列命令中的任何地方引用它。它也可以有一个全局范围。

那么，非递归CTE并不是用于快速查询的。它并不总是比替代品快，反之亦然。在这篇深入的文章中查看性能比较。

如何使用SQL CTE？

现在你知道了它是什么，它不是什么，现在是时候知道如何使用它了。让我们也把它分成两部分：什么会工作，什么不会工作。

在SQL CTE中会起作用的8件事

1.使用内联或外部列别名

SQL CTE支持2种形式的列别名。下面是第一个使用内联形式的。

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跟随一个SQL CTE

你之前看到的SQL CTE例子都是跟随SELECT语句。除了SELECT语句，你还可以使用INSERT, UPDATE, DELETE, 或MERGE。

下面是一个使用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用逗号分隔。你可以在上面的例子中看到2个CTE。并且会有以下的结果集:

4.多次引用一个SQL CTE

但前面的例子中还有更多的内容。PreviousProductCost CTE引用LatestProductCost CTE。然后，外层查询又引用了LatestProductCost CTE。你可以根据需要多次引用这样的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被用来接收2个存储过程参数，@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.在游标中使用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有一个锚点成员和一个递归成员。你可以用它来查询分层数据。例如，家庭树在本质上是分层的。

如果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

下面是上述查询的输出:

下面是这个查询中发生的情况:

母亲和父亲列是一个皇室成员的ID。
乔治王子（ID=26）显示在上面。它是CTE的第一个主播成员。
他的母亲是凯瑟琳（ID = 21），他的父亲是威廉王子（ID = 13）。这也是第二和第三位锚定成员。
然后，威廉王子的父母是戴安娜公主（ID = 7）和查尔斯王子（ID = 3）。这和下一个要点是CTE的递归成员的一部分。
同时，查尔斯王子的父母是伊丽莎白女王（ID = 2）和菲利普王子（ID = 1）。

但是请注意：一个不正确的递归CTE写法可能会导致无限循环。因此，为了防止这种情况，你可以添加MAXRECURSIONn，其中n是循环的数量。在查询的最后，在WHERE子句或最后一个JOIN子句之后加上这个。

很好!我们用SQL CTE得到了英国王室的分层列表。

4个在SQL CTE中不工作的东西

在SQL Server CTE中有一些规则。因此，在这一节中，我们将讨论那些不工作的事情。

让我们开始吧。

1.在WITH子句前没有分号

有时，如果CTE的WITH子句前面没有分号，你会遇到一个语法错误。这发生在你运行一批SQL语句的时候。下面是一个例子。

发生这种情况是因为WITH子句被用于其他目的，如表提示。在前面的语句中添加一个分号就可以解决这个问题。

根据你的编辑器，斜线也会出现在像你上面看到的那个CTE名称下。错误信息对如何解决这个问题已经很清楚了。

2.SQL CTE列的冲突

你会遇到问题，如果

锚点和递归成员中的列数不同。
列没有被命名
有重复的名字
列的数据类型在锚点和递归成员中不一样。

下面是一个因为列未命名而导致CTE语法错误的例子。

3.在外部查询之外重复使用SQL CTE名称

一个SQL CTE是不能重复使用的。我在前面已经提到了这一点，但我想进一步强调这一点。根据前面的例子，你不能在下一个SQL命令中引用InvoiceCTE。它将触发一个错误:

如果你在一个批次中的另一个查询中需要临时结果集，有几个选择。一个是临时表。或者使用多个非递归CTE，如果这更快的话。

4.嵌套的SQL CTE

嵌套的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;

在SQL CTE中不允许的其他事项

当在一个递归成员中发现这些关键字时:
- 顶部
- LEFT, RIGHT, 和 OUTER JOIN (但是允许INNER JOIN)
- GROUP BY和HAVING
- 子查询
- select distinct
使用标量聚合
使用SELECT INTO，带有查询提示的OPTION子句，以及FOR BROWSE。
没有TOP子句的ORDER BY

SQL CTE的专业编码技巧

在没有IntelliSense的情况下，输入以上所有的代码会很困难，而且容易出错。所以，如果你能将这些降到最低，并将编码速度提高4倍，为什么不采取呢？这就是为什么有Devart的SQL Complete。它是一个SQL Server Management Studio智能插件。它提供SQL智能提示、自动完成、重构、格式化和调试。

让我们看看它是如何与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;

利用表的建议，使用ij这样的代码段来表示INNER JOIN。SQL Complete将建议可能的连接表和列。因此，利用这些建议。

自己看吧。从连接这两个表开始。

然后，使用列选择器添加列。

SQL CTE是另一种表达带有临时结果集的查询的方式。这个标准提高了代码的可读性。

那么，为什么不今天就用SQL Complete试试SQL CTE呢？

SQL CTE：通过简单的例子一次性掌握它

SQL CTE：如何通过简单的例子一举掌握它

SQL CTE是另一种表达带有临时结果集的查询的方式。这个标准提高了代码的可读性。通过简单的例子来学习其中的内涵和外延。

SQL CTE有什么大问题？

但是，什么是SQL CTE？以及如何使用它？

什么是SQL CTE？

它是什么

那么，你为什么要在SQL中使用CTE？

它不是什么

如何使用SQL CTE？

在SQL CTE中会起作用的8件事

1.使用内联或外部列别名

2.SELECT, INSERT, UPDATE, DELETE, 或MERGE跟随一个SQL CTE

3.一个查询中的多个CTE

4.多次引用一个SQL CTE

5.在存储过程中使用SQL CTE并向其传递参数

6.在一个视图中使用SQL CTE

7.在游标中使用SQL CTE

8.在递归CTE中使用一个临时表

4个在SQL CTE中不工作的东西

1.在WITH子句前没有分号

2.SQL CTE列的冲突

3.在外部查询之外重复使用SQL CTE名称

4.嵌套的SQL CTE

在SQL CTE中不允许的其他事项

SQL CTE的专业编码技巧