SQL SERVER技術内幕之5 表表達式

    表表達式是一種命名的查詢表達式，代表一個有效的關系表。可以像其他表一樣，在資料處理語句中使用表表達式。SQL Server支援4種類型的表表達式:派生表(derived table)、公用表表達式(CTE,common table expression)、視圖以及内聯表值函數(inline TVF,inline table-valued function)。

    表表達式并不是實體上真實存在的什麼對象，它們是虛拟的。對于表表達式的查詢在資料庫引擎内部都将轉換為對底層對象的查詢。使用表表達式的好處通常展現在代碼的邏輯方面，而不是性能方面。

    1.派生表

    派生表(也稱為表子查詢)是在外部查詢的FROM子句中定義的，派生表的存在範圍為定義它的外部查詢，隻要外部查詢一結束，派生表也就不存在了。 定義派生表的查詢語句要寫在一對圓括号内，後面跟着AS子句和派生表的名稱。例如，以下代碼定義了一個名為USACusts的派生表，它是一個傳回所有美國客戶的查詢:

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

    要有效地定義任何類型的表表達式，查詢語句必須滿足三個要求:

    (1).不保證有一定的順序。表表達式代表的是一個表，而關系表中的行是沒有固定順序的。ANSI SQL不允許在用于定義表表達表的查詢語句中有ORDER BY子句，T-SQL大體上遵守了這一限制，隻有一個例外情況--當在語句中指定了TOP。在帶有TOP選項的查詢語句中，ORDER BY子句的邏輯目的隻有一個:為TOP選項定義要篩選出哪些行。

    (2).所有的列必須有名稱。

    (3).所有的列名必須是唯一的。

    1.1 配置設定列别名

    (1)用内聯别名的形式對派生表進行查詢

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

    (2)用外部命名格式對派生表進行查詢

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

    2.公用表表達式(CTE)

    以下代碼定義了一個名為USACusts的CTE,它的内部查詢傳回所有來自美國的客戶，外部查詢則選擇了CTE中的所有行，示例如下:

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

    2.1 配置設定列别名

    CTE也支援兩種格式的列别名命名方式--内聯格式和外部格式。對于内聯格式，要指定<expression> AS <column_alias>;對于外部格式，在CTE名稱後面的一對圓括号中指定目标列的清單。示例如下:

-- Inline column aliasing
WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
)

-- External column aliasing
WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)      

    2.2 定義多個CTE

    從表面來看，派生表和CTE之間的差別可能隻是語義方面。不過CTE具有幾個重要優勢。優勢之一就是:如果須要在一個CTE中引用另一個CTE,不須要像派生表那樣進行嵌套，相反，隻要簡單地在同一WITH子句中定義多個CTE,并用逗号把它們分隔開。每個CTE可以引用在它前面定義的所有CTE，而外部查詢則可以引用所有CTE。

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      

    2.3 CTE的多引用

    CTE是先定義再查詢，就外部查詢的FROM子句來說，CTE是已經存在的，是以，可以引用同一個CTE的多個執行個體。

    2.4 遞歸CTE

    定義一個遞歸CTE至少需要兩個查詢，第一個查詢稱為定位點成員(anchor member),第二個查詢稱為遞歸成員(recursive member)。定位點成員查詢隻會被調用一次。遞歸成員是一個引用了CTE名稱的查詢。對CTE名稱的引用代表的是在一個執行序列中邏輯上的"前一個結果集"。第一次調 用遞歸成員時，"前一個結果集"代表由定位成員傳回的結果集，之後每次調用遞歸成員時，對CTE名稱的引用代表對遞歸成員的前一次調用所傳回的結果集。在 查詢傳回的結果上，兩個成員查詢必須在列的個數和相應列的資料類型上保持相容。示例如下所示:

WITH EmpsCTE AS
(
  SELECT empid, mgrid, firstname, lastname
  FROM HR.Employees
  WHERE empid = 2(定位點成員)
  
  UNION ALL
  
  SELECT C.empid, C.mgrid, C.firstname, C.lastname
  FROM EmpsCTE AS P
    JOIN HR.Employees AS C
      ON C.mgrid = P.empid(遞歸成員)
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;      

    定位點成員對HR.Employees表進行查詢，隻傳回雇員等于2的記錄行。第一次調用遞歸成員，傳回雇員2的直接下屬，比如3和5。第二次調用遞歸成員，傳回雇員3和5的直接下屬，以此類推，直到遞歸成員傳回一個空的結果集。

    3.視圖

    到目前為止，我們讨論了兩種類型的表表達式(派生表和CTE)，它們的作用範圍都非常有限，僅限于在單個語句的範圍内使用。隻要包含這些表表達式的外部查詢完成操作，它們便會随之消失。而視圖和内聯表值函數(inline TVF)是兩種可重用的表表達式，它們的定義儲存在一個資料庫對象中，一旦建立，就些對象就是資料庫的永久部分。示例如下所示:

USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.USACusts') IS NOT NULL
  DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';      

    一般推薦在和視圖在關的應用上下文中應用避免使用SELECT *語句，列是在編譯視圖時進行枚舉的，新加的列可能不會自動添加到視圖中。如果在底層表中添加了列，而且在視圖中需要這些新加的列，則可以使用ALTER VIEW語句對視圖進行相應的修改。

    3.1 視圖選項

    (1)ENCRYPTION選項

    在建立或修改視圖、存儲過程、觸發器用使用者定義函數時，都可以使用ENCRYPTION選項。如果指定ENCRYPTION選項，SQL Server在内部會對定義對象的文本進行混淆處理，普通使用者通過任何目錄對象都無法直接看到這種經過混淆處理的文本，隻有特權使用者通過特殊手段才能通路建立對象的文本。以下代碼可以傳回對象的建立文本。

SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));
GO      

    但是當修改視圖定義，并指定ENCRYPTION選項時，視圖定義的文本則傳回NULL

ALTER VIEW Sales.USACusts WITH ENCRYPTION
AS      

    (2)SCHEMABINDING選項

    一旦指定了這個選項，被引用的對象就不能删除，被引用的列也不能删除或修改。

ALTER VIEW Sales.USACusts WITH SCHEMABINDING      

    (3)CHECK OPTION

    選項的目的是為了防止通過視圖執行的資料修改與視圖中設定的過濾條件發生沖突，如果沒有設定CHECK OPTION選項，則可以通過USACusts視圖插入一條UK的客戶，這樣的更新會傳遞到底層的資料表中。但是，修改後的客戶将不能在視圖中顯示出來，因為它已經不再滿足視圖的查詢過濾條件。如果想防止這種與視圖的查詢過濾條件相沖突的修改，隻須在定義視圖的查詢語句末尾加上WITH CHECK OPTION即可。

-- Add CHECK OPTION to the View
ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
WITH CHECK OPTION;
GO      

    4.内聯表值函數

    内聯表值函數是一種可重要的表表達式，能夠支援輸入參數，支援輸入參數以外，内聯表值函數在其他方面都與視圖類似。示例如下所示:

-- Creating fn_GetCustOrders function
USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
  DROP FUNCTION dbo.fn_GetCustOrders;
GO
CREATE FUNCTION dbo.fn_GetCustOrders
  (@cid AS INT) RETURNS TABLE
AS
RETURN
  SELECT orderid, custid, empid, orderdate, requireddate,
    shippeddate, shipperid, freight, shipname, shipaddress, shipcity,
    shipregion, shippostalcode, shipcountry
  FROM Sales.Orders
  WHERE custid = @cid;
GO      

-- Test Function
SELECT orderid, custid
FROM dbo.fn_GetCustOrders(1) AS CO;      

    5.APPLY運算符

    APPLY運算符支援兩種形式:CROSS APPLY和OUTER APPLY。APPLY運算符對兩個輸入表進行操作，其中第二個可以是一個表表達式，将它們分别稱為左表和右表。CROSS APPLY實作了一個邏輯處理步驟：将右表表達式應用到左表中的每一行，再将結果集組合起來，生成一個統一的結果表。示例如下所示：

    以下代碼使用CROSS APPLY傳回每個客戶最新的三個訂單：

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
  CROSS APPLY
    (SELECT TOP(3) orderid, empid, orderdate, requireddate 
     FROM Sales.Orders AS O
     WHERE O.custid = C.custid
     ORDER BY orderdate DESC, orderid DESC) AS A;      

    如果右表表達式傳回的是一個空集，CROSS APPLY則不會傳回相應左邊的資料行。如果要在右表表達式傳回空集時也照樣傳回相應左表中的行，則可以用OUTER APPLY運算符代替CROSS APPLY。

    總結

    借助表表達式可以簡化代碼，提高代碼的可維護性，還可以封裝查詢邏輯。當需要使用表表達式，而且不計劃重用它們的定義時，可以使用派生表或CTE。與派生表相比，CTE具有兩個優點:CTE不用像派生表那樣嵌套使用，此外，還可以引用同一CTE的多個執行個體，也派生表不能這麼用。

    當需要定義可重用的表表達式時，可以使用視圖和内聯表值函數。如果不須要支援輸入參數，則使用視圖，相反則使用内聯表值函數

轉載于:https://www.cnblogs.com/JustYong/p/4511955.html