查看: 187|回复: 0

[SQLServer] sql-(Cross||Outer)Apply

发表于 6 天前

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

  1. If OBJECT_ID('Orders') Is Not Null Drop Table Orders;
  2. If OBJECT_ID('Customers') Is Not Null Drop Table Customers;
  3. Go
  4. Create Table Customers
  5. (
  6. CustomerID Char(5) Not Null Primary Key,
  7. City Varchar(10) Not Null
  8. );
  9. Create Table Orders
  10. (
  11. OrderID Int Not Null Primary Key,
  12. CustomerID Char(5) Null References Customers(CustomerID)
  13. );
  14. Go
  15. Insert Into Customers(CustomerID,City) Values('FISSA','Madrid');
  16. Insert Into Customers(CustomerID,City) Values('FRNDO','Madrid');
  17. Insert Into Customers(CustomerID,City) Values('KRLOS','Madrid');
  18. Insert Into Customers(CustomerID,City) Values('MRPHS','Zion');
  19. Insert Into Orders(OrderID,CustomerID) Values(1,'FRNDO');
  20. Insert Into Orders(OrderID,CustomerID) Values(2,'FRNDO');
  21. Insert Into Orders(OrderID,CustomerID) Values(3,'KRLOS');
  22. Insert Into Orders(OrderID,CustomerID) Values(4,'KRLOS');
  23. Insert Into Orders(OrderID,CustomerID) Values(5,'KRLOS');
  24. Insert Into Orders(OrderID,CustomerID) Values(6,'MRPHS');
  25. Insert Into Orders(OrderID,CustomerID) Values(7,Null);
复制代码
  1. Select * From Customers
复制代码

  1. Select * From Orders
复制代码

  1. Select a.CustomerID,a.City,c.OrderID From Customers a
  2. Cross Apply
  3. (
  4. Select Top 1 * From Orders b
  5. Where a.CustomerID=b.CustomerID
  6. Order By OrderID Desc
  7. ) c
复制代码

  1. Select a.CustomerID,a.City,c.OrderID From Customers a
  2. Cross Apply
  3. (
  4. Select Top 2 * From Orders b
  5. Where a.CustomerID=b.CustomerID
  6. Order By OrderID Desc
  7. ) c
复制代码

  1. Select a.CustomerID,a.City,c.OrderID From Customers a
  2. Outer Apply
  3. (
  4. Select Top 1 * From Orders b
  5. Where a.CustomerID=b.CustomerID
  6. Order By OrderID Desc
  7. ) c
复制代码



回复

使用道具 举报