SQL Server 多表运算——表表达式（上）

表表达式是一种命名的查询表达式，代表一个有效的关系表。可以像其他表一样，在数据处理语句中使用表表达式。SQL Server支持4种类型的表表达式：派生表(derived table)、公用表表达式(CTE,common table expression)、视图，以及内联表值函数(inlineTVF, inline table-valued function) 。表表达式并不是物理上真实存在的什么对象，它们是虚拟的对于表表达式的查询在数据库引擎内部都将转换为对底层对象的查询。使用表表达式的好处通常体现再代码的逻辑方面，而不是性能方面。

 

一.派生表

派生表（也称为表子查询）是在外部查询的FROM子句中定义的。派生表的存在范围为定义它的外部查询，只要外部查询一结束，派生表也就不存在了。定义派生表的查询语句要写在一对圆括号内， 后面跟着 AS 子句和派生表的名称。 例如，以下代码定义了一个名为 USACusts 的派生表，它是一个返回所有美国客户的查询；外部查询则选择了派生表的所有行：

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

表表达式代表的是一个表， 而关系表中的行是没有固定顺序。正因为如此， ANSI SQL 不允许在用于定义表表达式的查询语句中有 ORDER BY子句。T-SQL 大体上遵守了这一限制， 只有一个例外情况一当在语句中指定了 TOP。如果用带有 TOP 和 ORDER BY子句的查询语句来定义表表达式， ORDER BY 保证只为 TOP 选项提供逻辑筛选数据的服务， 而不用于通常数据排序的目的。

如果须要用一个本身就引用了某个派生表的查询去定义另一个派生表， 就是嵌套派生表。例如，查询用于返回订单年份和该年处理的客户数，但要求每个订单 年份处理的客户数要多于70人：

SELECT orderyear,
       numcusts
FROM
(
    SELECT orderyear,
           COUNT(DISTINCT custid) AS numcusts
    FROM
    (SELECT YEAR(orderdate) AS orderyear, cust1d FROM sales.orders) AS D1
    GROUP BY orderyear
) AS D2
WHERE numcusts > 70;

在这个例子中，使用表表达式的目的是为了重用列别名，而不必在查询中重复冗长的表达式。但是，由于在派生表之间存在嵌套，也增加了代码的复杂性，直接重复表达式的方法似乎更简单：

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

派生表在外部查询的FROM子句中定义的，其逻辑处理顺序并不优先于外部查询。当对外部查询的FROM子句进行处理时，派生表其实并不存在。因此，如果须要引用派生表的多个实例，必须基于同一查询去定义多个派生表。

SELECT cur.orderyear,
       cur.numcusts AS curnumcusts,
       Prv.numcusts AS prvnumcusts,
       curnumcusts - prvnumcusts AS growth
FROM
(
    SELECT YEAR(orderdate) AS orderyear,
           COUNT(DISTINCT custid) AS numcusts
    FROM Sales.Orders
    GROUP BY YEAR(orderdate)
) AS cur
    LEFT OUTER JOIN
    (
        SELECT YEAR(orderdate) AS orderyear,
               COUNT(DISTINCT custid) AS numcusts
        FROM Sales.Orders
        GROUP BY YEAR(orderdate)
    ) AS Prv
        ON cur.orderyear = Prv.orderyear + 1;

 

二.公用表表达式(CTE)

公用表表达式(CTE,Common table expression)是和派生表很相似的另一种形式的表表达式。CTE是用WITH子句定义的，一般格式为：

WITH <CTE_Name>[(<target_colum_list>)]
AS 
(
    <inner_query_defining_CTE> 
)
<outer_query_against_CTE>; 

有效定义表表达式而须要遵守的所有规则，对定义CTE的内部查询也同样适用。对表表达式改写为CTE:

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

和派生表一样，一旦外部查询完成，CTE的生命期也就结束了。如果需要在一个CTE中引用另一个CTE，不需要像派生类那样进行嵌套，只需要在同一个WITH子句中定义多个CTE，并用逗号隔开。每个CTE可以引用前面定义的所有CTE，外部查询可以引用所有CTE。

WITH Cl AS 
(
    SELECT YEAR(orderdate) AS orderyear, custid FROM Sales.Orders 
),
C2 AS
(
    SELECT orderyear, COUNT(DISTINCT custid) AS numcusts FROM Cl GROUP BY orderyear 
)
SELECT orderyear, numcusts FROM C2 WHERE numcusts > 70; 

CTE是先定义， 再查询， 这样做带来的一个优点是：就外部查询的FROM子句来说，CTE是已经存在的。因此， 可以引用同一个CTE 的多个实例。

WITH YearlyCount AS 
(
    SELECT YEAR(orderdate) AS orderyear, 
        COUNT(DISTINCT custid) AS numcusts
    FROM Sales.Orders 
    GROUP BY YEAR(orderdate) 
)
SELECT Cur.orderyear, 
    Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts, 
    Cur.numcusts - Prv.numcusts AS growth 
FROM vearlycount AS Cur 
    LEFT OUTER JOIN Yearlycount AS Prv
      ON Cur.orderyear = Prv.orderyear + l;

CTE支持递归查询。定义一个递归CTE至少需要两个查询：第一个查询称为定位点成员，第二个查询称为递归成员。递归CTE的基本格式如下：

WITH <CTE_Name>[(<target_column_list>)] 
AS
(
    <anchor_member>
    UNION ALL
    <recursive_member>
)
<outer_query_against_CTE>;

定位点成员是一个返回有效关系结果表的查询，与用于定义非递归表表达式的查询类似。 定位点成员查询只被调用一次。递归成员是一个引用了CTE名称的查询。 对CTE名称的引用代表的是在一个执行序列中逻辑上的 “前一个结果集＂ 。 第一次调用递归成员时， 前一个结果集” 代表由定位点成员返回的任何结果集。 之后每次调用递归成员时， 对CTE名称的引用代表对递归成员的前一次调用所返回的结果集。 递归成员没有显式的递归终止检查（终止检查是隐式的）。 递归成员会一直被重复调用， 直到返回空的结果集或超出了某种限制条件。在查询返回的结果上， 两个成员查询必须在列的个数和相应列的数据类型上保持兼容。外部查询中的 CTE名称引用代表对定位点成员调用和所有对递归成员调用的联合结果集。以下代码演示了如何使用递归CTE来返回有关某个雇员(Don Funk, 雇员 ID为2)及其所有各级（直接或间接）下属的信息：

WITH EmpsCTE AS 
(
    SELECT empid, mgrid, firstname, lastname 
    FROM HR.Employees 
    WHERE empid = 2 
    UNION ALL 
    SELECT C.empid, C.mgrid, C.firstname, Clastname FROM EmpsCTE AS P 
    JOIN HR.Employees AS C ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname 
FROM EmpsCTE; 

定位点成员对HR.Employees表进行查询， 只返回雇员ID等于2的行：

SELECT empid, mgrid, firstname, lastname FROM HR.Employees 
WHERE empid = 2 

递归成员查询对CTE (代表前一次递归调用的结果集) 和Employees表进行联接， 返回在前一次递归调用的结果集中所返回雇员的直接下级：

SELECT C.empid, C.mgrid, C.firstname, Clastname FROM EmpsCTE AS P 
    JOIN HR.Employees AS C ON C.mgrid = P.empid

第一次调用递归成员，返回雇员 2 的直接下属： 雇员 3 和雇员 5。第二次调用递归成员， 返回雇员 3 和雇员 5的直接下属： 雇员 4、6、7、8、9。第3 次调用递归成员时， 没有更多的下级雇员， 递归成员返回一个空的结果集，递归至此结束。

 

 

好了，本篇文章就介绍到这儿，欢迎大家留言交流；喜欢或有帮助到您的话，点个赞或推荐支持一下！

 

转载于:https://www.cnblogs.com/johnvwan/p/9463788.html