sql-(Cross||Outer)Apply

2017-09-04 11:49:40来源:cnblogs.com作者:zhyue93人点击

分享

Apply - 涉及以下两个步骤中的一步或两步(取决于Apply的类型):
  1、A1:把右表表达式应用于左表的行
  2、A2:添加外部行
  Apply运算符把右表表达式应用于左输入的每一行。右表达式可以引用左输入中的列,对于左表中的每一行,都要计算一次右边输入的表达式。这一步会把左边的每一行和来自右表达式的响应行进行匹配,并将生成的结果集合合并起来,返回组合后的结果
  Cross Apply和Outer Apply总是包含步骤A1,只有Outer Apply才包含步骤A2
  如果内部(右)表表达式为外部(左)行返回的是空集,则Cross Apply不会返回该外部(左)行。而Outer Apply会返回这样的行,对于内表表达式的属性,则使用Null作为其占位符。

If OBJECT_ID('Orders') Is Not Null Drop Table Orders;If OBJECT_ID('Customers') Is Not Null Drop Table Customers;GoCreate Table Customers(    CustomerID Char(5) Not Null Primary Key,    City Varchar(10) Not Null);Create Table Orders(    OrderID Int Not Null Primary Key,    CustomerID Char(5) Null References Customers(CustomerID));GoInsert Into Customers(CustomerID,City) Values('FISSA','Madrid');Insert Into Customers(CustomerID,City) Values('FRNDO','Madrid');Insert Into Customers(CustomerID,City) Values('KRLOS','Madrid');Insert Into Customers(CustomerID,City) Values('MRPHS','Zion');Insert Into Orders(OrderID,CustomerID) Values(1,'FRNDO');Insert Into Orders(OrderID,CustomerID) Values(2,'FRNDO');Insert Into Orders(OrderID,CustomerID) Values(3,'KRLOS');Insert Into Orders(OrderID,CustomerID) Values(4,'KRLOS');Insert Into Orders(OrderID,CustomerID) Values(5,'KRLOS');Insert Into Orders(OrderID,CustomerID) Values(6,'MRPHS');Insert Into Orders(OrderID,CustomerID) Values(7,Null);
Select * From Customers

Select * From Orders

Select  a.CustomerID,a.City,c.OrderID From Customers aCross Apply(    Select Top 1 * From Orders b    Where a.CustomerID=b.CustomerID    Order By OrderID Desc) c

Select  a.CustomerID,a.City,c.OrderID From Customers aCross Apply(    Select Top 2 * From Orders b    Where a.CustomerID=b.CustomerID    Order By OrderID Desc) c

Select  a.CustomerID,a.City,c.OrderID From Customers aOuter Apply(    Select Top 1 * From Orders b    Where a.CustomerID=b.CustomerID    Order By OrderID Desc) c

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台