在 SELECT 查詢中使用表表達式

　　表表達式是一個命名的查詢表達式，傳回一個虛拟表。

一. 視圖（View）

　　視圖是由單個的 SELECT 查詢語句定義的。視圖中不可以有 ORDER BY 子句和 TOP 、OFFSET 等。

　　視圖也是一種表表達式，與前面幾種類型的表表達式相比，視圖的特點是預先定義并存儲在資料庫中。

　　視圖類似于導出的表和CTE，使用了視圖的查詢可以封裝并且更簡單。

CREATE VIEW EmpPhoneList AS SELECT empid, lastname, firstname, phone FROM Employees

二、TVF（Table-valued function，表值函數）

　　使用者可以自定義一個TVF，此函數将傳回 table 資料類型（虛拟表）。

　　SQL Server 提供兩種類型的TVF：

（1）内聯（Inline）TVF

　　對于内聯表值函數，沒有函數主體；表是單個 SELECT 語句的結果集。

　　下面的示例将建立一個内聯TVF。 對于銷售給商店的每個産品，該函數傳回三列，分别為 ProductID、Name 以及各個商店年初至今總數的累計 YTD Total 。

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) RETURNS TABLE RETURN (    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'    FROM Production.Product AS P    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID    WHERE C.StoreID = @storeid    GROUP BY P.ProductID, P.Name );

　　若要調用該函數，請運作此查詢。

SELECT * FROM Sales.ufn_SalesByStore (602);

（2）多語句（Multi-statement）TVF

　　對于多語句表值函數，在 BEGIN...END 語句塊中定義的函數體包含一系列 T-SQL 語句，這些語句可生成行并将其插入将傳回的表中。

　　視圖本身是不可以使用參數的，而表值函數可以看作是一個參數化的視圖。

三、派生表（Derived table）

　　派生表也稱為子查詢表或内聯視圖，是在外部查詢的 FROM 子句中定義的。

　　派生表隻存在于外部查詢。一旦外部查詢完成後，派生表就消失了。

　　下例是兩個嵌套的派表。

SELECT orderyear, cust_count FROM (SELECT  orderyear, COUNT(DISTINCT custid) AS cust_count FROM ( SELECT YEAR(orderdate) AS orderyear ,custid        FROM Orders) AS derived_table_1 GROUP BY orderyear) AS derived_table_2 WHERE cust_count > 80;

四、CTE（Common Table Expression，公用表表達式）

　　CTE作為一個表表達式，在一個查詢中被定義，而且能被緊随其後的首個查詢語句（在同一個批進行中）多次引用。

　　以下例子将建立一個多語句VTF，并在函數中使用CTE：

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER) RETURNS @retFindReports TABLE    EmployeeID int primary key NOT NULL,    FirstName nvarchar(255) NOT NULL,    LastName nvarchar(255) NOT NULL,    JobTitle nvarchar(50) NOT NULL,    RecursionLevel int NOT NULL ) --Returns a result set that lists all the employees who report to the --specific employee directly or indirectly.*/ BEGIN WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns    AS (        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n        FROM HumanResources.Employee e INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID        WHERE e.BusinessEntityID = @InEmpID        UNION ALL        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor            INNER JOIN EMP_cte            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode        ) -- copy the required columns to the result of the function   INSERT @retFindReports   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel   FROM EMP_cte   RETURN END;

　　示例建立了一個表值函數。此函數具有一個輸入參數 EmployeeID 而傳回直接或間接向指定員工報告的所有員工的清單。下面的示例調用此函數。

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel FROM dbo.ufn_FindReports(1);