SQL Server-表表達(dá)式基礎(chǔ)

表表達(dá)式

表表達(dá)式?jīng)]有任何的物理實(shí)例化，在查詢表表達(dá)式時(shí)它們是虛擬的，內(nèi)部查詢是非嵌套的，換句話說(shuō)，外部查詢和內(nèi)部查詢直接合并到一個(gè)底層對(duì)象的查詢中，使用表表達(dá)式的好處通常與代碼的邏輯方面有關(guān)，而與代碼的性能無(wú)關(guān)-摘抄自SQL Server 2012基礎(chǔ)教程。在使用表表達(dá)式時(shí)我們必須滿足以下3點(diǎn)要求，否則將會(huì)報(bào)錯(cuò)。我們下面來(lái)簡(jiǎn)短介紹下表表達(dá)式的4中類型。

（1）無(wú)法保證順序。

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

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

派生表

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

USE TSQL2012
GO

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

我們?cè)賮?lái)具體看下上述已經(jīng)明確說(shuō)過(guò)表表達(dá)式查詢滿足的條件，接下來(lái)我們進(jìn)行如下查詢：

USE TSQL2012
GO

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

當(dāng)我們?cè)谧硬樵冎刑砑覱RDER BY之后就出現(xiàn)如上錯(cuò)誤，這也就是說(shuō)的上述表表達(dá)式要求的第一點(diǎn)，表表達(dá)式作為關(guān)系表，因?yàn)殛P(guān)系在源于集合理論，所以無(wú)法保證輸出數(shù)據(jù)的順序，看到SQL Server 2012基礎(chǔ)教程中是這么說(shuō)，我也就這么理解，至于真正原因還是無(wú)法理解，反正在表表達(dá)式中千萬(wàn)不要進(jìn)行ORDER BY。關(guān)于要求的第二點(diǎn)和第三點(diǎn)就不用多說(shuō)，比如上述此時(shí)對(duì)表不起別名肯定會(huì)報(bào)錯(cuò)，還有當(dāng)對(duì)多個(gè)表進(jìn)行聯(lián)接時(shí)，表中列字段肯定有一樣的，為保證唯一，我們必須為列名起別名來(lái)解決不唯一的問(wèn)題。使用表表達(dá)式的好處之一就是在外部查詢的任何子句中，可以引用內(nèi)部查詢的SELECT子句中分配的列別名，如此這樣可以幫助我們繞開(kāi)在SELECT子句邏輯處理之前的查詢子句中（如WHERE、GROUP BY）無(wú)法引用SELECT子句中分配的列別名的實(shí)際問(wèn)題，到底是什么意思呢，我們知道進(jìn)行常規(guī)的查詢時(shí)，此時(shí)如WHERE、GROUP BY是在SELECT之前進(jìn)行，所以會(huì)導(dǎo)致我們對(duì)SELECT中的列通過(guò)WHERE、GROUP BY無(wú)法進(jìn)行引用，我們來(lái)看一下以下例子。

USE TSQL2012
GO

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

如上此時(shí)我們對(duì)SELECT中的orderyear通過(guò)GROUP BY來(lái)進(jìn)行分組，但是GROUP BY操作是在SELECT之前所以會(huì)導(dǎo)致出現(xiàn)如下錯(cuò)誤。

要解決這個(gè)問(wèn)題我們可以通過(guò)表表達(dá)式中的派生表來(lái)查詢

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

對(duì)于派生表可以引用參數(shù)來(lái)用于存儲(chǔ)過(guò)程或函數(shù)等變量或輸入?yún)?shù)，同時(shí)派生表可以進(jìn)行嵌套，如下：

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;

當(dāng)有多個(gè)表時(shí)這樣進(jìn)行嵌套時(shí)此時(shí)代碼會(huì)越來(lái)越復(fù)雜，冗長(zhǎng)的代碼不利于維護(hù)容易導(dǎo)致出錯(cuò)，同時(shí)也降低了代碼的可讀性。此時(shí)我們可以用表表達(dá)式的第2種形式CTE。

公用表表達(dá)式（CTE）

CTE通過(guò)WITH語(yǔ)句定義，具有如下常用形式。

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

我們來(lái)看一個(gè)關(guān)于CTE簡(jiǎn)單的例子

USE TSQL2012
GO

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

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

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

我們同樣可以類似實(shí)現(xiàn)派生表一樣的嵌套，如下：

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實(shí)現(xiàn)了和派生表同樣的結(jié)果，派生表和CTE其實(shí)只是在語(yǔ)義上有差異，但是相對(duì)于派生表最主要的優(yōu)勢(shì)在于不需要像派生表那樣需要多重嵌套，而CTE只要定義了就無(wú)需嵌套，每個(gè)CTE在代碼中以模塊化的方式分別出現(xiàn)。這中模塊化的方式和嵌套派生表方式相比，大大提高了代碼的可讀性和可維護(hù)性，若有多個(gè)表需要嵌套利用CTE來(lái)實(shí)現(xiàn)更加清爽并有助于代碼的清晰性。而對(duì)于派生表的另外一個(gè)優(yōu)勢(shì)在于就外部查詢的FROM子句而言，CTE在之前就已經(jīng)存在，因此可以引用同一個(gè)CTE的多個(gè)實(shí)例。

視圖（VIEW）

視圖和內(nèi)嵌表值函數(shù)是兩種可以重復(fù)使用的表表達(dá)式類型，其定義被存儲(chǔ)為數(shù)據(jù)庫(kù)對(duì)象，創(chuàng)建之后，這些對(duì)象是數(shù)據(jù)庫(kù)的永久部分，并且只有在顯式刪除它們時(shí)才能從數(shù)據(jù)庫(kù)中刪除。我們看下如何創(chuàng)建視圖并使用視圖。

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

創(chuàng)建視圖完之后視圖對(duì)象就在數(shù)據(jù)庫(kù)中已經(jīng)存在，此時(shí)我們?cè)賮?lái)查詢視圖

USE TSQL2012
GO

SELECT * FROM Sales.USACusts

內(nèi)嵌表值函數(shù)（TVF）

內(nèi)嵌表值函數(shù)是支持輸入?yún)?shù)的可重復(fù)使用的表表達(dá)式。除了支持輸入?yún)?shù)之外的其他所有方面都和視圖類似。我們來(lái)看下怎么創(chuàng)建內(nèi)嵌表值函數(shù)。

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

此時(shí)我們創(chuàng)建完畢TVF，我們接下來(lái)來(lái)調(diào)用自定義的TVF

USE TSQL2012
GO


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

上述我們?yōu)楸肀磉_(dá)式提供了一個(gè)別名，雖然不是必須的，但是推薦這樣做，因?yàn)樗勾a更具有可讀性和少出錯(cuò)誤。本節(jié)我們對(duì)表表達(dá)式的4種方式作了一下回顧，同樣我們來(lái)為這4種形式的表表達(dá)式來(lái)做個(gè)結(jié)論。

（1）表表達(dá)式可以簡(jiǎn)化代碼，提高代碼的可維護(hù)性和封裝查詢邏輯。

（2）當(dāng)需要使用表表達(dá)式并且不打算重復(fù)使用其定義時(shí)，可以使用派生表或CTE，而CTE對(duì)派生表具有更多優(yōu)勢(shì)不需要像派生表那樣嵌套CTE，使用CTE使代碼更加模塊化和便于維護(hù)，此外，還可以引用同一個(gè)CTE的多個(gè)實(shí)例，這一點(diǎn)是派生表無(wú)法實(shí)現(xiàn)的。

（3）當(dāng)需要使用表表達(dá)式并且需要定義可重復(fù)使用的表表達(dá)式時(shí)，可以使用視圖或內(nèi)嵌表值函數(shù)，當(dāng)不需要支持輸入?yún)?shù)時(shí)，可以使用視圖，否則，應(yīng)當(dāng)使用內(nèi)嵌表值函數(shù)（TVF）。