第五章 表表达式(2)

use TSQLFundamentals2008;

declare @name as nvarchar(max) = 'Anders Fan'

--5.4 内联表值函数
--除了支持输入参数之外,内联表值函数在其他方面与视图类似
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 * from sales.orders
where custid=@cid;
go

select orderid, custid
from dbo.FN_GetCustOrders(1);

--5.5 APPLY运算符
--与联接不同的是Cross Apply的右表表达式可能代表不同的数据行集合,
--可以在右边使用一个派生表,在派生表的查询中去引用左表列;也可以是
--内联表值函数,把左表中的列作为输入参数进行传递
--返回每个客户最新的三个订单
select c.custid, o.orderid, o.orderdate
from Sales.Customers as c
cross apply
(select top(3) * from Sales.Orders 
where c.custid = custid 
order by orderdate desc) as o

--Outer Apply运算符增加了另一个逻辑处理阶段;标识出让右表表达式返回
--空集的左表中的数据行,并把这些行作为外部行添加到结果集中,来自右表
--表达式的列用null作为占位符.从某种意义上讲,这个处理类似于做外联接
--中增加外部行的一步.
select c.custid, o.orderid, o.orderdate
from Sales.Customers as c
outer apply
(select top(3) * from Sales.Orders
where c.custid = custid
order by orderdate desc) as o

if OBJECT_ID('dbo.fn_TopOrders') is not null
drop function dbo.FN_TopOrders;
go

create function dbo.FN_TopOrders
(@cid as int, @tnum as int)
returns table
as return
(select top(@tnum) * from Sales.Orders
where custid=@cid
order by orderdate desc);

select c.custid, o.orderid, o.orderdate
from Sales.Customers as c
outer apply dbo.FN_TopOrders(c.custid, 3) as o