表表达式是一个命名的查询表达式，返回一个虚拟表。
一. 视图（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 AS 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.*/ AS 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 FROM HumanResources.Employee e INNER JOIN EMP_cte ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID ) -- 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); |
关于视图、派生表和CTE，可以参考