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

表表達(dá)式

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

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)！專注于網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、小程序定制開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了南通免費(fèi)建站歡迎大家使用！

（1）無法保證順序。

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

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

派生表

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

USE TSQL2012
GO

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

我們再來具體看下上述已經(jīng)明確說過表表達(dá)式查詢滿足的條件，接下來我們進(jìn)行如下查詢：

USE TSQL2012
GO

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

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

USE TSQL2012
GO

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

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

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

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

對于派生表可以引用參數(shù)來用于存儲(chǔ)過程或函數(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ì)越來越復(fù)雜，冗長的代碼不利于維護(hù)容易導(dǎo)致出錯(cuò)，同時(shí)也降低了代碼的可讀性。此時(shí)我們可以用表表達(dá)式的第2種形式CTE。

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

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

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

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

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

視圖（VIEW）

視圖和內(nèi)嵌表值函數(shù)是兩種可以重復(fù)使用的表表達(dá)式類型，其定義被存儲(chǔ)為數(shù)據(jù)庫對象，創(chuàng)建之后，這些對象是數(shù)據(jù)庫的永久部分，并且只有在顯式刪除它們時(shí)才能從數(shù)據(jù)庫中刪除。我們看下如何創(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)建視圖完之后視圖對象就在數(shù)據(jù)庫中已經(jīng)存在，此時(shí)我們再來查詢視圖

USE TSQL2012
GO

SELECT * FROM Sales.USACusts

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

內(nèi)嵌表值函數(shù)是支持輸入?yún)?shù)的可重復(fù)使用的表表達(dá)式。除了支持輸入?yún)?shù)之外的其他所有方面都和視圖類似。我們來看下怎么創(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，我們接下來來調(diào)用自定義的TVF

USE TSQL2012
GO


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

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

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

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

網(wǎng)頁標(biāo)題：SQLServer-表表達(dá)式基礎(chǔ)
網(wǎng)頁地址：http://muchs.cn/article4/ghijoe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站設(shè)計(jì)、建站公司、做網(wǎng)站、企業(yè)建站、商城網(wǎng)站、電子商務(wù)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)