完美世界官网,大主宰之灵路天蚕土豆,盗墓笔记txt全集下载

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案

如何快速上手SQLCTE?

譯者 | 陳峻

審校 | 孫淑娟

CTE是公用表表達(dá)式（common table expressions）的縮寫。它最初是在SQL:1999規(guī)范中被引入的。作為一種標(biāo)準(zhǔn)，它與子查詢和臨時(shí)表同源。

下面，我將通過一些簡單示例，向您介紹SQL CTE使用方法，以及如何使用工具來加快SQL CTE的相關(guān)編程實(shí)踐。

1.什么是SQL CTE?

CTE是由SELECT查詢派生出來的一個(gè)臨時(shí)被命名的結(jié)果集。它存在于諸如：SELECT、INSERT、UPDATE或MERGE等外部查詢的執(zhí)行范圍內(nèi)。既然是臨時(shí)的，那么在執(zhí)行完畢后，CTE就會(huì)消失。而且，在有限的范圍內(nèi)，您是無法重用CTE的。CTE不但能夠以遞歸的形式實(shí)現(xiàn)自我引用，而且可以讓用戶以如下代碼段的形式，使用WITH語句來創(chuàng)建CTE：

MS SQL
WITH [(column list)]
AS
(

)

2.為何要在SQL中使用CTE？

通常，在匯總數(shù)據(jù)或計(jì)算復(fù)雜公式時(shí)，我們需要將查詢分成不同的塊，以使得代碼簡潔與易懂。而CTE就能夠在此方面幫助到我們。下圖展示了我們將上述CTE語句實(shí)例化的逐行分析。這段代碼被分為了可讀性較強(qiáng)的內(nèi)部查詢和外部查詢兩個(gè)部分。

使用CTE的另一個(gè)場景是當(dāng)需要一個(gè)分層式列表（hierarchical list）時(shí)。對此，我將在下文，以示例的形式向您展示遞歸式的CTE。通常，SQL CTE可以分為遞歸式和非遞歸式兩種。不過，非遞歸式CTE并不會(huì)替換子查詢、派生表或臨時(shí)表。就上面的查詢示例而言，每一部分都在SQL腳本中有著自己的空間。例如，如果您在另一個(gè)查詢中需要臨時(shí)結(jié)果集的話，由于臨時(shí)表可以在腳本中涵蓋更大的范圍（例如：全局范圍），因此您可以在各條命令中的任何位置去引用它。當(dāng)然，非遞歸式CTE并不適用于極快的查詢需求。

3.如何使用SQL CTE？

下面，我們將從8個(gè)方面和您討論如何使用SQL CTE。

（1）使用內(nèi)聯(lián)或外部列的別名

SQL CTE支持兩種形式的列別名。下面展示了第一種--使用內(nèi)聯(lián)表單：

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關(guān)鍵字來定義SQL查詢中的列別名。其中，InvoiceMonth和Amount都是列別名。

而針對另一種列別名的形式，我們對上述代碼進(jìn)行了修改，并得到了下面的外部列別名代碼段：

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名稱之后被定義的，因此兩種查詢都將提供如下結(jié)果集：

（2）SELECT、INSERT、UPDATE、DELETE或MERGE

除了上面使用到的SELECT語句，您也可以使用INSERT、UPDATE、DELETE 或MERGE來開發(fā)SQL CTE實(shí)例。下面我們來看一個(gè)使用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）一個(gè)查詢中有多個(gè)CTE

您也可以在一個(gè)查詢中定義多個(gè)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;

左右滑動(dòng)查看完整代碼在上面的代碼段中，我們可以看到兩個(gè)CTE，它們是用逗號分隔的，其結(jié)果集為：

（4）多次引用一個(gè)SQL CTE

為了實(shí)現(xiàn)多次引用一個(gè)SQL CTE，我們可以讓PreviousProductCost CTE引用 LatestProductCost CTE，然后讓外部查詢再次引用LatestProductCost CTE。

（5）在存儲(chǔ)過程中使用SQL CTE并將各種參數(shù)傳遞給它

您還可以在某個(gè)存儲(chǔ)過程中使用SQL CTE，然后將存儲(chǔ)過程的各個(gè)參數(shù)值傳遞給它。請參見如下例子：

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

正如上面的代碼段所示，一個(gè)CTE用于接收兩個(gè)存儲(chǔ)過程參數(shù)，@productID和@increase。這將在ProductCostHistory表中添加一個(gè)新的行。

（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與指針一起使用，來循環(huán)遍歷各種結(jié)果。請參見如下例子：

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中使用臨時(shí)表

遞歸式CTE具有一個(gè)錨成員（anchor member）和一個(gè)遞歸成員。您可以使用它來查詢分層的數(shù)據(jù)。例如，家譜就是一種典型的分層結(jié)構(gòu)。至于CTE是使用普通表，還是臨時(shí)表，其實(shí)關(guān)系并不大。請參閱下面使用臨時(shí)表的示例：

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)出現(xiàn)在頂部。他是CTE的第一個(gè)錨定成員。
他的母親是凱瑟琳(ID = 21)，父親是威廉王子(ID = 13)。他們是第二和第三錨成員。
然后，威廉王子的父母是戴安娜王妃（ID = 7）和查爾斯王子（ID = 3）。他們和下一個(gè)節(jié)點(diǎn)都是CTE的遞歸成員中的一部分。
最下面，查爾斯王子的父母是伊麗莎白女王（ID = 2）和菲利普親王（ID = 1）。

值得注意的是：錯(cuò)誤地編寫遞歸式CTE，可能會(huì)導(dǎo)致無限的死循環(huán)。為此，您可以添加MAXRECURSION n，此處的n為循環(huán)次數(shù)。而且，您可以在WHERE子句或最后一個(gè)JOIN之后的查詢末尾添加它。

4.SQL CTE的使用坑點(diǎn)

下面，我們來討論有關(guān)SQL CTE的使用注意事項(xiàng)：

（1）WITH子句前沒有分號

如果CTE的WITH子句前面沒有分號，那么在您批量運(yùn)行SQL語句時(shí)，會(huì)被提示存在著語法錯(cuò)誤。請參見如下例子：

出現(xiàn)此類錯(cuò)誤的原因在于WITH子句被用于表提示等其他目的了。因此，我們只需在前面的語句中添加分號即可解決該問題。如果您使用的編輯器足夠智能，那么它往往會(huì)以波浪線的形式出現(xiàn)在CTE的名稱下方，以方便您及時(shí)發(fā)現(xiàn)錯(cuò)誤消息。

（2）SQL CTE的列沖突

如果你遇到下列問題，這往往源于未命名的列所導(dǎo)致的CTE語法錯(cuò)誤。

錨成員和遞歸成員中的列數(shù)不一致。
未命名的列。
重復(fù)的名稱。
錨成員和遞歸成員的列的數(shù)據(jù)類型不同。

請看如下示例：

（3）在外部查詢之外重用SQL CTE名稱

正如前文所說，SQL CTE是不可重用的。針對前面的例子，我們不能在下一個(gè)SQL命令中再次引用InvoiceCTE，否則就會(huì)觸發(fā)錯(cuò)誤。

如果您需要在另一個(gè)批量查詢中使用臨時(shí)結(jié)果集，那么請要么采用臨時(shí)表，要么使用更快的多個(gè)非遞歸式的CTE。

（4）嵌套SQL CTE

如果SQL CTE被嵌套的話，是不會(huì)起作用的。下面的代碼段示例就會(huì)導(dǎo)致多個(gè)語法錯(cuò)誤：

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中需要避免的其他方面

在遞歸成員中出現(xiàn)如下關(guān)鍵字：

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的專業(yè)編程技巧

在沒有智能感知（IntelliSense）的情況下，我們手動(dòng)鍵入上述代碼很可能會(huì)出錯(cuò)。因此，我們往往需要用到Devart的SQL Complete等工具。作為SQL Server Management Studio（簡稱SSMS）的智能加載項(xiàng)，它能夠提供SQL IntelliSense、自動(dòng)化完成、重構(gòu)、格式化、以及調(diào)試等功能。下面，讓我們來看看它是如何與SQL CTE協(xié)同工作的：

首先，在SSMS的查詢窗口中，請輸入cte并按下Tab鍵。如下代碼段將為您提供一個(gè)可以填寫的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之類的片段，來進(jìn)行INNER JOIN。該過程如下圖所示：

最后，請使用列選擇器去添加相應(yīng)的列。

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

譯者介紹

陳峻（Julian Chen），社區(qū)編輯，具有十多年的IT項(xiàng)目實(shí)施經(jīng)驗(yàn)，善于對內(nèi)外部資源與風(fēng)險(xiǎn)實(shí)施管控，專注傳播網(wǎng)絡(luò)與信息安全知識與經(jīng)驗(yàn)；持續(xù)以博文、專題和譯文等形式，分享前沿技術(shù)與新知；經(jīng)常以線上、線下等方式，開展信息安全類培訓(xùn)與授課。

文章題目：如何快速上手SQLCTE?
網(wǎng)站URL：http://m.fisionsoft.com.cn/article/djchhei.html