sql-(Cross||Outer)Apply

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;
Go

Create 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)
);
Go

Insert 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 a
Cross 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 a
Cross 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 a
Outer Apply
(
    Select Top 1 * From Orders b
    Where a.CustomerID=b.CustomerID
    Order By OrderID Desc
) c