如何快速上手SQL CTE?-云智核

当前位置：首页 >应用开发 >如何快速上手SQL CTE?

如何快速上手SQL CTE?

发布时间：2025-11-04 21:28:32 来源：云智核作者：数据库

译者 | 陈峻

审校 | 孙淑娟

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

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

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

MS SQL

WITH [(column list)]

(

)

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

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

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

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

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

MS SQL

USE WideWorldImporters;

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

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

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

MS SQL

USE WideWorldImporters;

-- Use an external column alias

WITH InvoiceCTE(InvoiceMonth, Amount)

(

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

由于列别名是在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;

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()

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

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

MS SQL

-- Getting the before and after product standard cost change

USE AdventureWorks;

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

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

（4）多次引用一个SQL CTE

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

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

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

MS SQL

USE AdventureWorks;

IF OBJECT_ID(dbo.uspInsertNewProductCost) IS NOT NULL

SET NOEXEC ON

CREATE PROCEDURE dbo.uspInsertNewProductCost

(

@productID INT,

@increase DECIMAL(3,2)

)

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;

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

（6）在视图中使用SQL CTE

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

MS SQL

USE WideWorldImporters;

CREATE VIEW dbo.vwYearlyInvoiceTotalsPerProduct

WITH InvoiceCTE(InvoiceYear, InvoiceMonth, StockItemID, Amount)

(

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

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

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

MS SQL

USE WideWorldImporters

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

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

)

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

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

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

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

下面，我们来讨论有关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)

(

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

(

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

FROM InvoiceCTE i

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

)

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

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

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

GROUP BY and HAVING

Subqueries

SELECT DISTINCT

在没有智能感知（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)

(

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

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

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

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

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

随便看看