收藏1 -SQL
為了使讀者對這些新引入的T-SQL特性有一個大概的了解，我先概括性地列出這些特性：
- APPLY Operator
- Common Table Expression
- PIVOT Operator
- TOP Clause Enhancement
- Ranking
- DDL Trigger
- Others
一、??????????? APPLY Operator
APPLY這個操作符被置于一個查詢的FROM語句中，對于查詢出的每條數據行，都去調用一個Table Value Function（TVF），并將TVF的數據附加在現有的查詢結果上。APPLY通常用于這樣的場景中：查詢的結果一部分包含在一個Table或者View中，另一部分則通過一個TVF來獲得，通過TVF獲得的記錄是基于Table或者View中每條記錄的某個Column的數據，也就是說我們把Table或者View的某個Column的值作為調用TVF的參數。這實際上將通過TVF獲得的Table作為現有Table或者View的Outer table，將它們連接（Join）在一起，而連接它們的Key就是作為TVF參數傳入的Column。
我們知道Join分為Inner Join和Outer Join，他們分別對應著CROSS APPLY和OUTER APPLY。如果對于某個條記錄，TVF發揮的是一個空的Rowset，對于CROSS APPLY，該記錄將不會出現在最終的結果中，而對于OUTER APPLY來說，最終的查詢結果將包含該條記錄，只是基于TVF的Column的值為NULL。
可能文字描述太過抽象，我們現在通過例子來進一步理解APPLY Operator。下面的例子基于的Database是SQL Server 2005 的Sample Database：AdventureWorks。（注：后續的例子如未作特殊的說明，均使用的是該Database）。
我們首先創建一個TVF：dbo.fn_getproduct。根據Product ID獲得產品信息。
IF?EXISTS?(SELECT?*?FROM?sysobjects?WHERE?type?=?'IF'?AND?name?=?'fn_getproduct')????BEGIN
????????DROP??Function??dbo.fn_getproduct
????END
GO
CREATE?Function?dbo.fn_getproduct?
(
????@product_id?Int
)
RETURNS?TABLE
AS?RETURN?
SELECT?*?FROM?Production.Product?WHERE?ProductID?=?@product_id
GO
然后我們做如下的查詢：對Production.WorkOrder作查詢，并列出對應的Product的信息：
FROM?Production.WorkOrder?WorkOrder
CROSS?APPLY?dbo.fn_getproduct(WorkOrder.ProductID)
下面是查詢結果：
我們可以看到ProductNumber和Name兩個Column實際上是來自TVF中的，其余才是來自于Production.WorkOrder。如果把TVF看作一個Table，通過查詢結果我們可以看出，上面的查詢相當于把這個Table和Production.WorkOrder通過ProductID作了一個Join。到底是Inner Join，還是Outer Join？我們對這個TVF作如下修改，使其在正常的情況下返回一個空的結果集(WHERE ProductID = @product_id * -1)：
IF?EXISTS?(SELECT?*?FROM?sysobjects?WHERE?type?=?'IF'?AND?name?=?'fn_getproduct')
????BEGIN
????????DROP??Function??dbo.fn_getproduct
????END
GO
CREATE?Function?dbo.fn_getproduct?
(
????@product_id?Int
)
RETURNS?TABLE
AS?RETURN?
SELECT?*?FROM?Production.Product?WHERE?ProductID?=?@product_id?*?-1
GO
再次運行上面的查詢，我們會發現最終返回的結果為空：
看來CROSS APPLY使用的是Inner Join。
我們現在來試試OUTER APPLY：
SELECT?WorkOrderID,WorkOrder.ProductID,ProductNumber,[Name],OrderQtyFROM?Production.WorkOrder?WorkOrder
OUTER?APPLY?dbo.fn_getproduct(WorkOrder.ProductID)
下面是最終的輸出結果，我們發現所有的Order記錄被返回，通過TVF獲得的ProductNumber和Name的值為NULL。這充分說明了OUTER APPLY采用的是OUTER JOIN。
二、??????????? Common Table Expression
Common Table Expression（CTE）可以看成是一個臨時創建的View，他的生命周期僅僅限于當前Context。一旦CTE被創建，你可以將它當成一般的Table，大部分基于Table的操作都可以運用于CTE。下面是創建CTE的語法結構：
WITH?cte_name(column?name?list)AS
(
?????query
)
E.G.
AS
(
????SELECT?*?FROM?Production.Product?WHERE?Color?=?'Black'
)
SELECT?*?FROM?CTE_Black_Product
CTE具有廣泛的運用，他往往具有將問題化繁為簡的魔力。下面介紹幾個典型的運用：
1．????????? 1.??????????? 將復雜的Aggregate置于CTE中，將復雜的問題分解為多個步驟。
如果我們現在需要統計每個客戶發出的訂單數量（相關數據存儲于Sales.SalesOrderHeader
中），同時輸出客戶的個人信息（相關數據存儲于Sales.Customer中）。雖然這樣的功能很簡單，但他體現了一種思想，把一部完成略顯復雜的功能進程分解成多個簡單的步驟。
AS
(
????SELECT?CustomerID,?Count(*)?As?OrderCount
????FROM?Sales.SalesOrderHeader
????GROUP?BY?CustomerID
)
SELECT?Sales.Customer.CustomerID,?AccountNumber,OrderCount
FROM?Sales.Customer?INNER?JOIN?CTE_SalesOrder_Count
ON?CTE_SalesOrder_Count.CustomerID?=?Sales.Customer.CustomerID
2．????????? 使用CTE代替自連接，以便更易于理解。
假設我們有一個Product表用于存儲每個Product的信息，每個Product有一個唯一標識Product_ID和一個不唯一的Product_Name。由于不同的Product可能重名，倘若我們有這樣的一個需求：需要將重名的記錄（除了具有最小ID的那個）刪除，從而保證其名稱的唯一性。我們來看看如何保這些需要上出的記錄篩選出來。Product表的記錄如下，ID為1和4的兩條記錄重名，現在我們的目的是把ID為4的記錄篩選出來。
在不考慮CTE的情況下，我們通過下面的SQL實現這個功能，這個SQL采用了自連接。雖然SQL看起來很簡潔，但是相信有一些人第一次看到這樣一個SQL，不能立即理解。
FROM?dbo.PRODUCT
WHERE?PRODUCT_ID?NOT?IN
(
????SELECT?MIN(PRODUCT_ID)
????FROM?dbo.PRODUCT?p
????WHERE?dbo.PRODUCT.PRODUCT_NAME?=?p.PRODUCT_NAME
)
但是如果我們采用了CTE，通過下面一段SQL來實現，雖然代碼多了點，但是從語義上看要易于理解一點：首先把重名的選出來，在和Product作一次連接。
AS
(
????SELECT?MIN(PRODUCT_ID)?AS?PRODUCT_ID,PRODUCT_NAME
????FROM?dbo.PRODUCT
????GROUP?BY?PRODUCT_NAME
????HAVING?COUNT(*)>1
)
SELECT?dbo.PRODUCT.PRODUCT_ID,?dbo.PRODUCT.PRODUCT_NAME
FROM?dbo.PRODUCT?
INNER?JOIN?CTE_PRODUCT
ON?CTE_PRODUCT.PRODUCT_NAME?=?dbo.PRODUCT.PRODUCT_NAME
AND?dbo.PRODUCT.PRODUCT_ID?>?CTE_PRODUCT.PRODUCT_ID
3．????????? 用于具有層次結構記錄的遞歸查詢
比如一個公司的員工體系就是一個包含上下級關系的具有層次化的樹形結構。假設我們有如下一個EMPLOYEE表，通過REPORT_TO體現每個員工的上下級關系（假設Empoyee_Name具有唯一性）。
我們現在的需求是：列出員工A的所有下級。
為了實現這樣的一個功能，我們需要以一種特殊的結構來創建CTE：
WITH?CTE_EMPLOYEE(EMPLOYEE_ID,?EMPLOYEE_NAME,REPORT_TO)
AS
(
????SELECT?*?
????FROM?dbo.EMPLOYEE
????WHERE?EMPLOYEE_NAME?=?'A'
????
????UNION?ALL
????
????SELECT?dbo.EMPLOYEE.*
????FROM?dbo.EMPLOYEE
????JOIN?CTE_EMPLOYEE
????ON?dbo.EMPLOYEE.REPORT_TO?=?CTE_EMPLOYEE.EMPLOYEE_ID
)
SELECT?*?
FROM?CTE_EMPLOYEE
WHERE?EMPLOYEE_NAME?>?'A'
OR?EMPLOYEE_NAME?<?'A'
我們發現CTE中主體部分由兩個SELECT語句組成，我們把第一個叫做Anchor Member（AM），AM不會遞歸，只會執行一次，本例中篩選出了級別最高的A；另一個SELECT語句叫做Recursive Member（RM），RM通過CTE本身和EMPLOYEE表建立連接，所以RM會采用遞歸的方式執行。
轉載于:https://www.cnblogs.com/Gsun/archive/2009/12/24/1631494.html
《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀總結
- 上一篇： 访问ASP.NET临时文件夹的权限问题
- 下一篇： 项目与团队管理体会