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，如下所示：

微软 SQL

1 WITH <cte_name>[(column list)]
2 AS
3(
4 <inner query defining the CTE>
5)
6<outer query: SELECT | INSERT | UPDATE | DELETE | MERGE>

为了说明，请参阅下面的 SQL CTE 剖析：

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

在汇总数据或计算复杂公式时，将查询分成块总是好的。

为什么？

它简化了您的代码。这使它更容易阅读和理解。CTE 会为您做到这一点。看到上面的示例了吗？它将作业分为2个：内部查询和外部查询。具有 1 个 CTE 的内部查询也是最简单的。

因此，简而言之，CTE 可以帮助您的代码块更具可读性。

使用 CTE 的另一个原因是当您需要分层列表时。递归 CTE 可以帮助您。您将在后面的部分中看到这样的示例。

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

它不是什么

现在，让我们揭开关于 CTE的神秘面纱。因为你可能听说过关于它的传闻。

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

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

如何使用 SQL CTE？

现在您知道它是什么，不是什么，是时候知道如何使用它了。让我们也将其分为 2：什么会起作用，什么不会起作用。

在 SQL CTE 中可以使用的 8 件事

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

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

微软 SQL

1 USE WideWorldImporters;

2 GO

3

4-- Use an inline column alias

5 WITH InvoiceCTE AS 

6(

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

8  FROM Sales.InvoiceLines il

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

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

11 GROUP BY MONTH(i.InvoiceDate)

12  )

13 SELECT InvoiceMonth, Amount 

14 FROM InvoiceCTE

15 ORDER BY InvoiceMonth;

使用 AS 关键字定义 SQL 查询中的列别名。在上面的代码中，InvoiceMonth 和 Amount 是列别名。

列别名的另一种形式是外部形式。请参阅下面使用它的相同代码的修订版：

微软 SQL

1 USE WideWorldImporters;

2 GO

3

4 -- Use an external column alias

5 WITH InvoiceCTE(InvoiceMonth, Amount) 

6 AS 

7  (

8    SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice)

9     FROM Sales.InvoiceLines il

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

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

12    GROUP BY MONTH(i.InvoiceDate)

13 )

14   SELECT InvoiceMonth, Amount 

15 FROM InvoiceCTE

16 ORDER BY InvoiceMonth;

这一次，列别名是在 CTE 名称之后定义的。两个查询都将具有以下结果集：

2. SELECT、INSERT、UPDATE、DELETE 或 MERGE 遵循 SQL CTE

您看到的前面的 SQL CTE 示例遵循 SELECT 语句。除了 SELECT 语句，您还可以使用 INSERT、UPDATE、DELETE 或 MERGE。

下面是一个使用 INSERT 的示例：

微软 SQL

1-- Get the latest product cost and add a 2% increase in price in product cost history

2USE AdventureWorks;

3GO

4

5DECLARE @productID INT = 703;

6

7WITH LatestProductCost AS 

8(

9    SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost

10    FROM Production.ProductCostHistory pch

11    WHERE pch.ProductID = @productID

12    ORDER BY pch.StartDate DESC

13)

14INSERT INTO Production.ProductCostHistory 

15(ProductID, StartDate, EndDate, StandardCost, ModifiedDate)

16SELECT 

17 @productID

18 ,DATEADD(d,1,lpc.EndDate)

19 ,DATEADD(d,366,lpc.EndDate)

20 ,(lpc.StandardCost * 0.02) + lpc.StandardCost

21 ,GETDATE()

22 FROM LatestProductCost lpc;

23

3. 一个查询中有多个 CTE

您还可以在查询中定义超过 1 个 CTE。这是一个例子：

微软 SQL

1-- Getting the before and after product standard cost change

2USE AdventureWorks;

3GO

4

5DECLARE @productID INT = 711;

6

7WITH LatestProductCost AS 

8 (

9    SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCost

10    FROM Production.ProductCostHistory pch

11   WHERE pch.ProductID = @productID

12    ORDER BY pch.StartDate DESC

13 ),

14PreviousProductCost AS 

15 (

16    SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCost

17    FROM Production.ProductCostHistory pch

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

19    WHERE pch.ProductID = @productID

20      AND pch.StartDate < lpc.StartDate

21   ORDER BY pch.StartDate DESC

22 )

23 SELECT 

24 lpc.ProductID

25 ,p.Name AS Product

26 ,lpc.StandardCost AS LatestCost

27 ,lpc.StartDate

28 ,ppc.StandardCost AS PreviousCost

29 FROM LatestProductCost lpc

30 INNER JOIN PreviousProductCost ppc ON lpc.ProductID = ppc.ProductID

31 INNER JOIN Production.Product p ON lpc.ProductID = p.ProductID

32 WHERE lpc.ProductID = @productID;

多个 CTE 用逗号分隔。您可以在上面的示例中看到 2 个 CTE。它将具有以下结果集：

4. 多次引用一个 SQL CTE

但是前面的示例还有更多内容。PreviousProductCost CTE 引用 LatestProductCost CTE。然后，外部查询再次引用了 LatestProductCost CTE。您可以根据需要多次引用这样的 CTE。

5. 在存储过程中使用 SQL CTE 并将参数传递给它

您还可以在存储过程中使用 SQL CTE。然后，将存储过程参数值传递给它是可能的。

这是一个例子：

微软 SQL

1 USE AdventureWorks;

2 GO

3

4IF OBJECT_ID('dbo.uspInsertNewProductCost') IS NOT NULL

5   SET NOEXEC ON

6 GO

7 CREATE PROCEDURE dbo.uspInsertNewProductCost

8 (

9   @productID INT,

10    @increase DECIMAL(3,2)

11 )

12 AS

13 SET NOCOUNT ON;

14

15 WITH LatestProductCost AS 

16 (

17  SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost

18    FROM Production.ProductCostHistory pch

19   WHERE pch.ProductID = @productID

20   ORDER BY pch.StartDate DESC

21 )

22

INSERT INTO Production.ProductCostHistory 

23 (ProductID, StartDate, EndDate, StandardCost, ModifiedDate)

24SELECT 

25 @productID

26 ,DATEADD(d,1,lpc.EndDate)

27 ,DATEADD(d,366,lpc.EndDate)

28 ,(lpc.StandardCost * @increase) + lpc.StandardCost

29 ,GETDATE()

30 FROM LatestProductCost lpc;

31

32 GO

在上面，一个 CTE 用于接收 2 个存储过程参数，@productID 和 @increase。这将在 ProductCostHistory 表中添加一个新行。

6. 在视图中使用 SQL CTE

您还可以在视图中使用 SQL CTE。这是一个例子：

MS SQL

1USE WideWorldImporters;

2GO

3

4CREATE VIEW dbo.vwYearlyInvoiceTotalsPerProduct

5AS

6

7WITH InvoiceCTE(InvoiceYear, InvoiceMonth, StockItemID, Amount) 

8AS 

9(

10    SELECT YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice)

11   FROM Sales.InvoiceLines il

12   INNER JOIN Sales.Invoices i ON i.InvoiceID = il.InvoiceID

13   GROUP BY YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID

14)

15SELECT i.InvoiceYear, i.InvoiceMonth, si.StockItemName, i.Amount 

16FROM InvoiceCTE i

17INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID

18GO

7. 在游标中使用 SQL CTE

您还可以将 SQL CTE 与游标一起使用。然后，循环遍历结果。这是一个例子：

MS SQL

1 USE WideWorldImporters

2 GO

3

4 DECLARE @invoiceMonth TINYINT

5 DECLARE @amount MONEY

6

7 DECLARE invoice_cursor CURSOR FOR

8

9    WITH InvoiceCTE AS 

10    (

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

12        FROM Sales.InvoiceLines il

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

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

15       GROUP BY MONTH(i.InvoiceDate)

16  )

17  SELECT InvoiceMonth, Amount 

18   FROM InvoiceCTE

19  ORDER BY InvoiceMonth

20

21 OPEN invoice_cursor

22 FETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amount

23 WHILE @@fetch_status = 0 

24 BEGIN  

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

26    PRINT 'Amount: ' + CAST(@amount AS VARCHAR)

27

28   FETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amount

29 END

30

31 CLOSE invoice_cursor

32 DEALLOCATE invoice_cursor

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

递归 CTE 具有锚成员和递归成员。您可以使用它来查询分层数据。例如，家谱本质上是分层的。

CTE 使用普通表还是临时表都没有关系。请参阅下面使用临时表的示例：

MS SQL

1

-- British Royal family

2

CREATE TABLE dbo.RoyalFamily

3

(

4

    ID INT NOT NULL,

5

    Name VARCHAR(60) NOT NULL,

6

    Father INT,

7

    Mother INT

8

    CONSTRAINT PK_RoyalFamily_ID PRIMARY KEY (ID)

9

)

10

GO

11

12

INSERT INTO dbo.RoyalFamily

13

(ID, Name, Father, Mother)

14

VALUES

15

(1,'Philip',NULL,NULL),

16

(2,'Queen Elizabeth II',NULL,NULL),

17

(3,'Charles',1,2),

18

(4,'Anne',2,1),

19

(5,'Andrew',2,1),

20

(6,'Edward',2,1),

21

(7,'Diana',NULL,NULL),

22

(8,'Camilla',NULL,NULL),

23

(9,'Mark Philips',NULL,NULL),

24

(10,'Timothy Laurence',NULL,NULL),

25

(11,'Sarah',NULL,NULL),

26

(12,'Sophie',NULL,NULL),

27

(13,'William',3,7),

28

(14,'Harry',3,7),

29

(15,'Peter Philips',9,4),

30

(16,'Zara Tindall',9,4),

31

(17,'Beatrice',5,11),

32

(18,'Eugenie',5,11),

33

(19,'Louise',6,12),

34

(20,'James',6,12),

35

(21,'Catherine',NULL,NULL),

36

(22,'Meghan',NULL,NULL),

37

(23,'Autumn Philips',NULL,NULL),

38

(24,'Mike Tindall',NULL,NULL),

39

(25,'Jack Brooksbank',NULL,NULL),

40

(26,'George',13,21),

41

(27,'Charlotte',13,21),

42

(28,'Louis',13,21),

43

(29,'Archie Harrison Mountbatten-Windsor',14,22),

44

(30,'Savannah',15,23),

45

(31,'Isla',15,23),

46

(32,'Mia Grace',24,16),

47

(33,'Lena',24,16);

48

49

DECLARE @id INT = 26;  -- Prince George

50

51

WITH Ancestor(ID) AS 

52

(

53

    -- First anchor member returns the royal family member in question

54

    SELECT ID 

55

    FROM dbo.RoyalFamily 

56

    WHERE ID = @id

57

    UNION

58

    -- Second anchor member returns the father

59

    SELECT Father

60

    FROM dbo.RoyalFamily 

61

    WHERE ID = @id

62

    UNION

63

    -- Third anchor member returns the mother

64

    SELECT Mother

65

    FROM dbo.RoyalFamily

66

    WHERE ID = @id

67

    UNION ALL

68

    -- First recursive member returns male ancestors of the previous generation

69

    SELECT rf.Father

70

    FROM RoyalFamily rf

71

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

72

    UNION ALL

73

    -- Second recursive member returns female ancestors of the previous generation

74

    SELECT rf.Mother

75

    FROM RoyalFamily rf

76

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

77

)

78

SELECT DISTINCT rf.ID, rf.Name, rf.Mother, rf.Father

79

FROM RoyalFamily rf

80

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

81

ORDER BY rf.ID DESC

82

以下是上述查询的输出：

以下是此查询中发生的情况：

• 母亲和父亲列是王室成员的 ID。

• 乔治王子 (ID = 26) 出现在顶部。它是 CTE 的第一个锚定成员。

• 他的母亲是凯瑟琳 (ID = 21)，父亲是威廉王子 (ID = 13)。这些也是第二和第三锚成员。

• 然后，威廉王子的父母是戴安娜王妃（ID = 7）和查尔斯王子（ID = 3）。这个和下一个要点是 CTE 的递归成员的一部分。

• 与此同时，查尔斯王子的父母是伊丽莎白女王（ID = 2）和菲利普亲王（ID = 1）。

但请注意：错误编写的递归 CTE 可能会导致无限循环。因此，为了保护您免受这种情况的影响，您可以添加MAXRECURSION n，其中 n 是循环数。在 WHERE 子句或最后一个 JOIN 之后的查询末尾添加它。

伟大的！我们使用 SQL CTE 来获取英国王室的等级列表。

在 SQL CTE 中不起作用的 4 件事

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

1

WITH InvoiceCTE(InvoiceMonth, StockItemID, Amount) 

2

AS 

3

(

4

    SELECT MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice)

5

    FROM Sales.InvoiceLines il

6

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

7

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

8

    GROUP BY MONTH(i.InvoiceDate), il.StockItemID

9

),

10

AverageAmountPerMonth AS

11

(

12

    SELECT InvoiceMonth, AVG(Amount) AS Average

13

    FROM (WITH InvoiceAmountPerMonth 

14

          AS

15

          (

16

            SELECT i.InvoiceMonth, si.StockItemName,  i.Amount 

17

            FROM InvoiceCTE i

18

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

19

          )

20

         )

21

)

22

SELECT * FROM AverageAmountPerMonth;

SQL CTE 中不允许的其他事情

• 在递归成员中找到这些关键字时：

  • 最佳

  • LEFT、RIGHT 和 OUTER JOIN（但允许 INNER JOIN）

  • GROUP BY 和 HAVING

  • 子查询

  • 选择不同的

• 使用标量聚合

• 使用 SELECT INTO、带有查询提示的 OPTION 子句和 FOR BROWSE。

• 没有 TOP 子句的 ORDER BY

SQL CTE 的专业编码技巧

在没有 IntelliSense 的情况下键入上述所有代码可能很困难且容易出错。因此，如果您可以将这些最小化并将编码速度提高 4 倍，为什么不采用呢？这就是为什么有 Devart 的 SQL Complete 的原因。这是一个 SQL Server Management Studio 智能加载项。它提供SQL IntelliSense、自动完成、重构、格式化和调试。

让我们看看它是如何与 SQL CTE 一起工作的。

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

然后，重命名 CTE。

然后，编辑 CTE，直到代码如下所示：

MS SQL

1

WITH InvoiceCTE(InvoiceMonth, Amount) 

2

AS 

3

(

4

    SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice)

5

    FROM Sales.InvoiceLines il

6

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

7

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

8

    GROUP BY MONTH(i.InvoiceDate)

9

)

10

SELECT InvoiceMonth, Amount 

11

FROM InvoiceCTE

12

ORDER BY InvoiceMonth;

13

利用表格建议并使用像ij这样的片段来进行 INNER JOIN。SQL Complete 将建议可能连接的表和列。所以，利用这些。

你自己看。从加入 2 个表开始。

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

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

那么，为什么不今天尝试使用 SQL Complete 的 SQL CTE 呢？

原文标题：SQL CTE: How to Master It in One Sitting With Easy Examples

原文作者：埃德温·桑切斯

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