日本综合一区二区|亚洲中文天堂综合|日韩欧美自拍一区|男女精品天堂一区|欧美自拍第6页亚洲成人精品一区|亚洲黄色天堂一区二区成人|超碰91偷拍第一页|日韩av夜夜嗨中文字幕|久久蜜综合视频官网|精美人妻一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務時間：8:30-17:00
你可能遇到了下面的問題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
如何快速上手SQLCTE?

譯者 | 陳峻

審校 | 孫淑娟

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

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

1.什么是SQL CTE? 

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

MS SQL
WITH [(column list)]
AS
(

)

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

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

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

3.如何使用SQL CTE？ 

下面，我們將從8個方面和您討論如何使用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都是列別名。

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

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實例。下面我們來看一個使用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，它們是用逗號分隔的，其結(jié)果集為：

（4）多次引用一個SQL CTE

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

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

您還可以在某個存儲過程中使用SQL CTE，然后將存儲過程的各個參數(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

正如上面的代碼段所示，一個CTE用于接收兩個存儲過程參數(shù)，@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）在指針（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中使用臨時表

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

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

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

4.SQL CTE的使用坑點 

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

（1）WITH子句前沒有分號

如果CTE的WITH子句前面沒有分號，那么在您批量運行SQL語句時，會被提示存在著語法錯誤。請參見如下例子：

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

（2）SQL CTE的列沖突

如果你遇到下列問題，這往往源于未命名的列所導致的CTE語法錯誤。

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

請看如下示例：

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

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

如果您需要在另一個批量查詢中使用臨時結(jié)果集，那么請要么采用臨時表，要么使用更快的多個非遞歸式的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.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)鍵字：

  • TOP

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

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

SQL Complete將建議您連接的表和列，因此請利用其表格建議，并使用ij之類的片段，來進行INNER JOIN。該過程如下圖所示：

最后，請使用列選擇器去添加相應的列。

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

譯者介紹

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


分享名稱：如何快速上手SQLCTE?
文章鏈接：http://www.dlmjj.cn/article/djchhei.html