[size=large]APPLY
APPLY运算符把右表表达式应用左输入中的每一行。右表达式可以引用左输入中的列,对于左表中的每一行,都要计算一次右边输入中表达式。这一步会把左边每一行和来自右表表达式的相应行进行匹配,并将生成的结果集合并起来,返回组合后结果。
APPLY有两种类型:
1、CROSS APPLY 2、OUTER APPLY
两者的区别是如果内部(右)表表达式为外部(左)行返回的是空集,则CROSS APPLY不会返回该外部(左)行。而OUTER APPLY 会返回这样的行,对于内表表达式的属性,则使用NULL作为其占位符
举例 :
创建一个顾客 表和一个订单员[/size]
APPLY运算符把右表表达式应用左输入中的每一行。右表达式可以引用左输入中的列,对于左表中的每一行,都要计算一次右边输入中表达式。这一步会把左边每一行和来自右表表达式的相应行进行匹配,并将生成的结果集合并起来,返回组合后结果。
APPLY有两种类型:
1、CROSS APPLY 2、OUTER APPLY
两者的区别是如果内部(右)表表达式为外部(左)行返回的是空集,则CROSS APPLY不会返回该外部(左)行。而OUTER APPLY 会返回这样的行,对于内表表达式的属性,则使用NULL作为其占位符
举例 :
创建一个顾客 表和一个订单员[/size]
SET NOCOUNT ON;
IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID('dbo.Customers') IS NOT NULL DROP TABLE dbo.Customers;
GO
CREATE TABLE dbo.Customers
(
customerid CHAR(5) NOT NULL PRIMARY KEY,
city VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL PRIMARY KEY,
customerid CHAR(5) NULL REFERENCES Customers(customerid)
);
GO
INSERT INTO dbo.Customers(customerid, city) VALUES('FISSA', 'Madrid');
INSERT INTO dbo.Customers(customerid, city) VALUES('FRNDO', 'Madrid');
INSERT INTO dbo.Customers(customerid, city) VALUES('KRLOS', 'Madrid');
INSERT INTO dbo.Customers(customerid, city) VALUES('MRPHS', 'Zion');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(1, 'FRNDO');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(2, 'FRNDO');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(3, 'KRLOS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(4, 'KRLOS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(5, 'KRLOS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(6, 'MRPHS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(7, NULL);
SELECT * FROM dbo.Customers;
SELECT * FROM dbo.Orders;
--要求查询为每个顾户返回具有最大订单ID的两个订单
-- Two most recent orders for each customer
SELECT C.customerid, C.city, A.orderid
FROM dbo.Customers AS C
CROSS APPLY
(SELECT TOP (2) O.orderid, O.customerid
FROM dbo.Orders AS O
WHERE O.customerid = C.customerid
ORDER BY orderid DESC) AS A;
-- Two most recent orders for each customer,
-- including customers that made no orders
SELECT C.customerid, C.city, A.orderid
FROM dbo.Customers AS C
OUTER APPLY
(SELECT TOP (2) O.orderid, O.customerid
FROM dbo.Orders AS O
WHERE O.customerid = C.customerid
ORDER BY orderid DESC) AS A;