- 当前位置：首页 >系统运维 >如何快速上手SQL CTE?
如何快速上手SQL CTE?
发布时间：2025-11-04 12:56:28 来源：技术快报 作者：系统运维
译者 | 陈峻
审校 | 孙淑娟
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>1.2.3.4.5.6.7. 2.为何要在SQL中使用CTE？通常，在汇总数据或计算复杂公式时，我们需要将查询分成不同的块，以使得代码简洁与易懂。而CTE就能够在此方面帮助到我们。下图展示了我们将上述CTE语句实例化的逐行分析。这段代码被分为了可读性较强的内部查询和外部查询两个部分。
使用CTE的另一个场景是当需要一个分层式列表（hierarchical list）时。对此，我将在下文，以示例的形式向您展示递归式的CTE。通常，SQL CTE可以分为递归式和非递归式两种。不过，非递归式CTE并不会替换子查询、派生表或临时表。就上面的查询示例而言，每一部分都在SQL脚本中有着自己的空间。例如，如果您在另一个查询中需要临时结果集的话，b2b信息网由于临时表可以在脚本中涵盖更大的范围（例如：全局范围），因此您可以在各条命令中的任何位置去引用它。当然，非递归式CTE并不适用于极快的查询需求。
3.如何使用SQL CTE？下面，我们将从8个方面和您讨论如何使用SQL CTE。
（1）使用内联或外部列的别名
SQL CTE支持两种形式的列别名。下面展示了第一种--使用内联表单：
复制MS SQL
USE WideWorldImporters;GO
-- Use an inline column aliasWITH InvoiceCTE AS(SELECT MONTH(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) ASAmount
FROM Sales.InvoiceLinesil
INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN 1/1/2013 AND 12/31/2013GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth,Amount
FROMInvoiceCTE
ORDER BY InvoiceMonth;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.上述代码使用了AS关键字来定义SQL查询中的列别名。其中，InvoiceMonth和Amount都是列别名。
而针对另一种列别名的形式，我们对上述代码进行了修改，并得到了下面的外部列别名代码段：
复制MS SQL
USE WideWorldImporters;GO
-- Use an external column aliasWITH InvoiceCTE(InvoiceMonth, Amount)AS(SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice)FROM Sales.InvoiceLinesil
INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN 1/1/2013 AND 12/31/2013GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth,Amount
FROMInvoiceCTE
ORDER BY InvoiceMonth;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.由于列别名是在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;GO
DECLARE @productID INT = 703;WITH LatestProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCostFROM Production.ProductCostHistorypch
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;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.（3）一个查询中有多个CTE
您也可以在一个查询中定义多个CTE。我们来看下面的例子：
复制MS SQL
-- Getting the before and after product standard cost changeUSE AdventureWorks;GO
DECLARE @productID INT = 711;WITH LatestProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCostFROM Production.ProductCostHistorypch
WHERE pch.ProductID =@productID
ORDER BY pch.StartDate DESC),PreviousProductCost AS(SELECT TOP 1 pch.ProductID, pch.StartDate, pch.StandardCostFROM Production.ProductCostHistorypch
INNER JOIN LatestProductCost lpc ON pch.ProductID = lpc.ProductIDWHERE pch.ProductID =@productID
AND pch.StartDate < lpc.StartDateORDER BY pch.StartDate DESC)SELECTlpc.ProductID,p.Name ASProduct
,lpc.StandardCost ASLatestCost
,lpc.StartDate,ppc.StandardCost ASPreviousCost
FROMLatestProductCost lpc
INNER JOIN PreviousProductCost ppc ON lpc.ProductID = ppc.ProductIDINNER JOIN Production.Product p ON lpc.ProductID = p.ProductIDWHERE lpc.ProductID = @productID;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.左右滑动查看完整代码在上面的代码段中，我们可以看到两个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 NULLSET NOEXEC ONGO
CREATE 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.ProductCostHistorypch
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;GO1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.正如上面的代码段所示，一个CTE用于接收两个存储过程参数，@productID和@increase。这将在ProductCostHistory表中添加一个新的行。
（6）在视图中使用SQL CTE
您还可以在视图中使用SQL CTE。请参见如下例子：
复制MS SQL
USE WideWorldImporters;GO
CREATE VIEW dbo.vwYearlyInvoiceTotalsPerProductASWITH InvoiceCTE(InvoiceYear, InvoiceMonth, StockItemID, Amount)AS(SELECT YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice)FROM Sales.InvoiceLinesil
INNER 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.AmountFROMInvoiceCTE i
INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemIDGO1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.（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_cursor1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.（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))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 GeorgeWITH Ancestor(ID) AS(-- First anchor member returns the royal family member in questionSELECTID
FROM dbo.RoyalFamilyWHERE ID =@id
UNION-- Second anchor member returns the fatherSELECTFather
FROM dbo.RoyalFamilyWHERE ID =@id
UNION-- Third anchor member returns the motherSELECTMother
FROM dbo.RoyalFamilyWHERE ID =@id
UNIONALL
-- First recursive member returns male ancestors of the previous generationSELECT rf.FatherFROMRoyalFamily rf
INNER JOIN Ancestor a ON rf.ID = a.IDUNIONALL
-- Second recursive member returns female ancestors of the previous generationSELECT rf.MotherFROMRoyalFamily rf
INNER JOIN Ancestor a ON rf.ID = a.ID)SELECT DISTINCT rf.ID, rf.Name, rf.Mother, rf.FatherFROMRoyalFamily rf
INNER JOIN Ancestor a ON rf.ID = a.IDORDER BY rf.ID DESC1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.我们使用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.InvoiceLinesil
INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN 1/1/2013 AND 12/31/2013GROUP BY MONTH(i.InvoiceDate), il.StockItemID),AverageAmountPerMonth AS(SELECT InvoiceMonth, AVG(Amount) ASAverage
FROM (WITH InvoiceAmountPerMonth
AS (SELECT i.InvoiceMonth, si.StockItemName, i.AmountFROMInvoiceCTE i
INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID )))SELECT * FROM AverageAmountPerMonth;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.（5）在SQL CTE中需要避免的其他方面
在递归成员中出现如下关键字：
TOPLEFT, 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.InvoiceLinesil
INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceIDWHERE i.InvoiceDate BETWEEN 1/1/2013 AND 12/31/2013GROUP BY MONTH(i.InvoiceDate))SELECT InvoiceMonth,Amount
FROMInvoiceCTE
ORDER BY InvoiceMonth;1.2.3.4.5.6.7.8.9.10.11.12.13.SQL Complete将建议您连接的表和列，因此请利用其表格建议，并使用ij之类的片段，来进行INNER JOIN。该过程如下图所示：
最后，请使用列选择器去添加相应的列。
原文链接：https://dzone.com/articles/sql-cte-how-to-master-it-in-one-sitting-with-easy
译者介绍陈峻 （Julian Chen），51CTO社区编辑，具有十多年的IT项目实施经验，善于对内外部资源与风险实施管控，专注传播网络与信息安全知识与经验；持续以博文、专题和译文等形式，分享前沿技术与新知；经常以线上、线下等方式，开展信息安全类培训与授课。
- 三星卡刷XP教程（详细步骤教你如何在三星手机上刷入XP系统，享受原汁原味的XP体验）
- 以XP盘安装教程（快速轻松安装WindowsXP系统）
- 华硕飞行堡垒8装机教程（详细指导及注意事项，助您顺利完成华硕飞行堡垒8的组装）
- LenovoZ41-70评测（了解LenovoZ41-70的卓越性能与便携性，体验无与伦比的使用体验）
- 电脑检测关键信号错误及其修复方法（解决关键信号错误的有效技巧与注意事项）
- 清华同方锋锐K560——超越期待的卓越表现（探索科技与智慧的完美融合）
- 使用U盘安装Win7系统教程（轻松安装Win7系统，节省时间和精力）
- AOCi2369v6
- U盘装双系统教程（简单易行的U盘装双系统方法）
- 迅雷450白金版（解放下载速度，享受畅快网络世界）
- 尼康200.500镜头评测（探究尼康200.500镜头的特点和优势）
- 评测NVIDIAGeForceGT610显卡的性能和功能（揭示GeForceGT610显卡的优点和限制）
- is916en量产教程（掌握is916en量产，加速产品交付的速度与质量）
- 如何设置虚拟内存（教程）
- 电脑剪映短剧教程（以电脑剪映为背景，教你制作出精彩的短剧作品）
- 详解u安装系统教程（轻松掌握u安装系统的步骤与技巧）
- 探索Fiilbestie耳机的音质和功能（一款值得期待的高性能耳机）
- iPhone6已停用解锁教程（简单有效的解锁方法，快速恢复使用）
- 如何调暗台式电脑屏幕？（简单方法让您享受更舒适的视觉体验）
- 装Win8.1系统教程（详细图文教程，手把手教你安装Win8.1系统）
随便看看
- Copyright © 2025 Powered by 如何快速上手SQL CTE?,技术快报 滇ICP备2023006006号-46sitemap