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

在上一篇文章，我们讨论了两种类型的表表达式（派生表和CTE), 它们的作用范围都非常有限，仅限于在单个语句的范围内使用。只要包含这些表表达式的 外部查询完成操作，它们便随之消失了。 这就意味着派生表和 CTE都是不可重用的。

视图和内联表值函数( inline TVF)是两种可重用的表表达式， 它们的定义储存在一个数据库对象中。一旦创建， 这些对象就是数据库的永久部分； 只有用删除语句显式删除，它们才会从数据库中移除。在其他很多方面， 视图和内联表值函数的处理方式都类似于派生表和CTE。

三.视图

派生表（也称为表子查询）是在外部查询的FROM子句中定义的。派生表的存在范围为定义它的外部查询，只要外部查询一结束，派生表也就不存在了。定义派生表的查询语句要写在一对圆括号内， 后面跟着 AS 子句和派生表的名称。 例如，以下代码定义了一个名为 USACusts的视图

CREATE VIEW Sales.USACusts 
AS 
SELECT 
custid, companyname, contactname, contacttitle, address, 
city, region, postalcode, country, phone, fax 
FROM sales.customers 
WHERE country s N'USA';       

在定义表表达式的查询语句中不允许出现ORDER BY子句，试图创建一个有序视图的想法也不合理，因为这违反了关系模型定义的关系的基本属性。 如果为了数据展示的目的， 确实须要从视图中返回有序的数据行， 应该在使用视图的外部查询中指定一个数据展示用的ORDER BY子句。

SELECT custid, companyname, region 
FROM Sales.USACusts 
ORDER BY region;       

四.内联表值函数

内联表值函数是一种可重用的表表达式， 能够支持输入参数。 除了支持输入参数以外， 内联表值函数在其他方面都与视图相似。

CREATE FUNCTION dbo.fn_Getcustorders 
(@cid AS INT) RETURNS TABLE 
AS 
RETURN 
    SELECT orderid, custid, emp记，orderdate, requireddate,
        shippeddate, shipperid, freight, shipname, shipaddress, 
        shipcity, shipregion, shippostalcode, shipcountry 
    FROM Sales.Orders 
WHERE custid = @cid;       

以下代码对这个函数进 行查询， 请求返回由客户1下的所有订单：

SELECT orderid, custid FROM dbo.fn_GetCustOrders(l) AS CO;       

和其他表一样，也能够在联接中引用内联表值函数。例如，以下查询将内联表值函数（返 回客户l的订单）与Sales.OrderDetails表进行联接，对客户1的订单和相关的订单详情进行匹配：

SELECT co.orderid, co.custid,  OD.productid,  OD.qty 
FROM dbo.fn_Getcustorders(l) AS co 
JOIN sales.orderoetails AS OD
ON co.orderid =  OD.orderid;      

五.APPLY运算符

APPLY运算符是在SQL Server 2005中引入的一个非标准表运算符。 和其他表运算符一样， 这个运算符也在查询的FROM子句中使用。 APPLY运算符支持两种形式： CROSS APPLY和OUTER APPLY。CROSS APPLY只实现了一个逻辑查询处理步骤，而OUTER APPLY实现了两个步骤。

APPLY运算符对两个输入表进行操作， 其中第二个可以是一个表表达式， 我们将它们分别称为左表和右表 。右表通常是一个派生表或内联表值函数 。 CROSS APPLY运算符实 现了一个逻辑查询处理步骤： 把右表表达式应用到左表中的每一行， 再把结果集组合起来， 生成一个统一的结果表。

CROSS APPLY运算符与交叉联接非常类似， 从某种意义上讲也确实如此。 例如， 以下两个查询会返回同样的结果集：

SELECT S.shipperid, E.empid
FROM sales.Shippers AS s 
CROSS JOIN HR.Employees AS E;

SELECT s.shipperid, E.empid FROM Sales.Shippers AS S 
CROSS APPLY HR.Employees AS E;      

但是， 与联接不同的是， 当使用CROSS APPLY操作符时， 对于左表中的每一行， 右表表达式可能代表不同的数据行织合。 为此， 可以在右边使用一个派生表，在派生表的查询中去引用左表列； 也可以使用内联表值函数， 把左表中的列作为输入参数进行传递。例如，以下代码使用CROSSAPPLY运算符返回每个客户最新的三个订单：

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;      

可以把上面查询中的表表达式A看作是一个相关表子查询。就逻辑查询处理来说，右表 表达式（在这个例子中是派生表）要应用于Customers表的每一行。注意，在派生表的查询过滤条件中引用了来自左表的列C.custid。派生表为左表当前行的客户返回他的3个最新订单。对左表的每一行应用派生表，CROSS APPLY运算符就可以返回每个客户最新的3个订单。

如果右表表达式返回的是一个空集，CROSS APPLY运算符则不会返回相应左边的数据行。例如，如客户22和客户57没有下过订单。对千这两种情况，派生表返回的都是空集。因此，这两个客户不会返回到输出中。如果要在右表表达式返回空集时也照样返回

相应左表中的行，则可以用OUTER APPLY运算符代替CROSS APPLY。OUTER APPLY 运算符增加了另一个逻辑处理阶段：标识出让右表表达式返回空集的左表中的数据行，并把这些行作为外部行添加到结果表中，来自右表表达式的列用NULL作为占位符。从某种意义上讲，这个处理步骤类似于左外联接中增加外部行的那一步。例如，以下代码返回每个客户最新的三个订单，输出中也包括没有下过订单的客户：

SELECT C.custid,
       A.orderid,
       A.orderdate
FROM sales.customers AS C
    OUTER 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;      

这一次，虽然客户22和客户57都没有下过订单， 但在输出中还会包括他们，只是值为NULL。

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

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