第五章 表表达式(2)
use TSQLFundamentals2008;declare @name as nvarchar(max) = 'Anders Fan'--5.4 内联表值函数--除了支持输入参数之外,内联表值函数在其他方面与视图类似if OBJECT_ID('dbo.fn_getcustorders') is not nulldrop function dbo.FN_GetCustOrders;gocreate function dbo.FN_GetCustOrders(@cid as int) returns tableas return select * from sales.orderswhere custid=@cid;goselect orderid, custidfrom dbo.FN_GetCustOrders(1);--5.5 APPLY运算符--与联接不同的是Cross Apply的右表表达式可能代表不同的数据行集合,--可以在右边使用一个派生表,在派生表的查询中去引用左表列;也可以是--内联表值函数,把左表中的列作为输入参数进行传递--返回每个客户最新的三个订单select c.custid, o.orderid, o.orderdatefrom Sales.Customers as ccross 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.orderdatefrom Sales.Customers as couter apply(select top(3) * from Sales.Orderswhere c.custid = custidorder by orderdate desc) as oif OBJECT_ID('dbo.fn_TopOrders') is not nulldrop function dbo.FN_TopOrders;gocreate function dbo.FN_TopOrders(@cid as int, @tnum as int)returns tableas return(select top(@tnum) * from Sales.Orderswhere custid=@cidorder by orderdate desc);select c.custid, o.orderid, o.orderdatefrom Sales.Customers as couter apply dbo.FN_TopOrders(c.custid, 3) as o