SQL Server-表表達式基礎

表表達式

表表達式沒有任何的物理實例化，在查詢表表達式時它們是虛擬的，內部查詢是非嵌套的，換句話說，外部查詢和內部查詢直接合并到一個底層對象的查詢中，使用表表達式的好處通常與代碼的邏輯方面有關，而與代碼的性能無關-摘抄自SQL Server 2012基礎教程。在使用表表達式時我們必須滿足以下3點要求，否則將會報錯。我們下面來簡短介紹下表表達式的4中類型。

（1）無法保證順序。

（2）所有列都必須具有名稱。

（3）所有列名都必須是唯一的。

派生表

派生表（也稱為子查詢表）是在外部查詢的FROM子句中定義的，它們存在的范圍是外部查詢。一旦外部查詢完成后，派生表就消失了。我們看一個簡單的派生表的例子。

USE TSQL2012
GO

SELECT *FROM(
    SELECT * FROM Sales.Customers WHERE country = N'USA') AS USACusts;

我們再來具體看下上述已經明確說過表表達式查詢滿足的條件，接下來我們進行如下查詢：

USE TSQL2012
GO

SELECT *FROM(
    SELECT * FROM Sales.Customers WHERE country = N'USA' ORDER BY custid) AS USACusts;

當我們在子查詢中添加ORDER BY之后就出現如上錯誤，這也就是說的上述表表達式要求的第一點，表表達式作為關系表，因為關系在源于集合理論，所以無法保證輸出數據的順序，看到SQL Server 2012基礎教程中是這么說，我也就這么理解，至于真正原因還是無法理解，反正在表表達式中千萬不要進行ORDER BY。關于要求的第二點和第三點就不用多說，比如上述此時對表不起別名肯定會報錯，還有當對多個表進行聯接時，表中列字段肯定有一樣的，為保證唯一，我們必須為列名起別名來解決不唯一的問題。使用表表達式的好處之一就是在外部查詢的任何子句中，可以引用內部查詢的SELECT子句中分配的列別名，如此這樣可以幫助我們繞開在SELECT子句邏輯處理之前的查詢子句中（如WHERE、GROUP BY）無法引用SELECT子句中分配的列別名的實際問題，到底是什么意思呢，我們知道進行常規的查詢時，此時如WHERE、GROUP BY是在SELECT之前進行，所以會導致我們對SELECT中的列通過WHERE、GROUP BY無法進行引用，我們來看一下以下例子。

USE TSQL2012
GO

SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS custids
FROM Sales.Orders
GROUP BY orderyear

如上此時我們對SELECT中的orderyear通過GROUP BY來進行分組，但是GROUP BY操作是在SELECT之前所以會導致出現如下錯誤。

要解決這個問題我們可以通過表表達式中的派生表來查詢

USE TSQL2012
GO

SELECT orderyear, COUNT(DISTINCT custid) AS custids
FROM (SELECT  YEAR(orderdate) AS orderyear, custid FROM Sales.Orders) AS  SO
GROUP BY orderyear

對于派生表可以引用參數來用于存儲過程或函數等變量或輸入參數，同時派生表可以進行嵌套，如下：

USE TSQL2012
GO


SELECT orderyear, numcusts
FROM (
    SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 
    FROM (
        SELECT YEAR(orderdate) AS orderyear, custid 
        FROM Sales.Orders) AS D1 
        GROUP BY orderyear)AS D2
WHERE numcusts > 70;

當有多個表時這樣進行嵌套時此時代碼會越來越復雜，冗長的代碼不利于維護容易導致出錯，同時也降低了代碼的可讀性。此時我們可以用表表達式的第2種形式CTE。

公用表表達式（CTE）

CTE通過WITH語句定義，具有如下常用形式。

WITH <CTE_NAME>[(<target_column_list>)]
AS
(       <inner_query_defining_CTE>       )<outer_query_against_CTE>

我們來看一個關于CTE簡單的例子

USE TSQL2012
GO

WITH USACusts AS
(
    SELECT custid, companyname
    FROM Sales.Customers
    WHERE country = N'USA')
SELECT * FROM USACusts

和派生表相同，一旦外部查詢完成后，CTE馬上就會消失。在CTE中我們同樣可以使用參數，如下：

USE TSQL2012
GO

DECLARE @empid AS INT = 3;

WITH C AS
(
    SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders
    WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear

我們同樣可以類似實現派生表一樣的嵌套，如下：

USE TSQL2012
GO


WITH C1 AS
(
    SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders
),C2 AS
(
    SELECT orderyear,COUNT(DISTINCT custid) AS numcusts
    FROM C1
    GROUP BY orderyear
)

SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70

這里我們利用CTE實現了和派生表同樣的結果，派生表和CTE其實只是在語義上有差異，但是相對于派生表最主要的優勢在于不需要像派生表那樣需要多重嵌套，而CTE只要定義了就無需嵌套，每個CTE在代碼中以模塊化的方式分別出現。這中模塊化的方式和嵌套派生表方式相比，大大提高了代碼的可讀性和可維護性，若有多個表需要嵌套利用CTE來實現更加清爽并有助于代碼的清晰性。而對于派生表的另外一個優勢在于就外部查詢的FROM子句而言，CTE在之前就已經存在，因此可以引用同一個CTE的多個實例。

視圖（VIEW）

視圖和內嵌表值函數是兩種可以重復使用的表表達式類型，其定義被存儲為數據庫對象，創建之后，這些對象是數據庫的永久部分，并且只有在顯式刪除它們時才能從數據庫中刪除。我們看下如何創建視圖并使用視圖。

USE TSQL2012
GO

IF OBJECT_ID('Sales.USACusts') IS NOT NULL
    DROP VIEW Sales.USACusts;
GO

CREATE VIEW Sales.USACusts
AS

SELECT custid, companyname, contactname, contacttitle, [address]
FROM Sales.Customers
WHERE country = N'USA'GO

創建視圖完之后視圖對象就在數據庫中已經存在，此時我們再來查詢視圖

USE TSQL2012
GO

SELECT * FROM Sales.USACusts

內嵌表值函數（TVF）

內嵌表值函數是支持輸入參數的可重復使用的表表達式。除了支持輸入參數之外的其他所有方面都和視圖類似。我們來看下怎么創建內嵌表值函數。

USE TSQL2012
GO


IF OBJECT_ID('dbo.GetCustOrders') IS NOT NULL
    DROP FUNCTION dbo.GetCustOrders;
GO

CREATE FUNCTION dbo.GetCustOrders(@cid AS INT) RETURNS TABLE
AS RETURN
    SELECT orderid, custid, empid, orderdate, requireddate, shippeddate, shipperid, shipcity,
            shipaddress, shipregion, freight
    FROM Sales.Orders
    WHERE custid = @cid
GO

此時我們創建完畢TVF，我們接下來來調用自定義的TVF

USE TSQL2012
GO


SELECT orderid, custid
FROM dbo.GetCustOrders(1) AS O;

上述我們為表表達式提供了一個別名，雖然不是必須的，但是推薦這樣做，因為它使代碼更具有可讀性和少出錯誤。本節我們對表表達式的4種方式作了一下回顧，同樣我們來為這4種形式的表表達式來做個結論。

（1）表表達式可以簡化代碼，提高代碼的可維護性和封裝查詢邏輯。

（2）當需要使用表表達式并且不打算重復使用其定義時，可以使用派生表或CTE，而CTE對派生表具有更多優勢不需要像派生表那樣嵌套CTE，使用CTE使代碼更加模塊化和便于維護，此外，還可以引用同一個CTE的多個實例，這一點是派生表無法實現的。

（3）當需要使用表表達式并且需要定義可重復使用的表表達式時，可以使用視圖或內嵌表值函數，當不需要支持輸入參數時，可以使用視圖，否則，應當使用內嵌表值函數（TVF）。