日期:2014-05-16 浏览次数:20389 次
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;