查询某个客户最新的两个订单

declare @i int
set @i = 0
declare @temp table
(
 id int,
 customerid nvarchar(500),
 city nvarchar(500),
 orderid int
)
insert @temp
select top 100 percent ROW_NUMBER() over (order by c.customerid, o.orderid desc) as id, c.CustomerID, City, o.OrderID from Customers as c inner join
Orders as o
on c.CustomerID = o.CustomerID
order by id
declare @temp1 table
(
 id int,
 customerid nvarchar(500)
)
insert @temp1
select min(id), customerid from @temp group by customerid

declare @temp2 table
(
 id int,
 customerid nvarchar(500)
)
insert @temp2
select * from @temp1
union
select t1.* from(
select id + 1 as id, customerid from @temp1) t1
inner join @temp t on t1.customerid = t.customerid
and t1.id = t.id

select  temp.CustomerID, City, temp.OrderID from @temp as temp inner join
@temp2 as temp2
on temp.id = temp2.id
order by temp.customerid, orderid desc

简单办法：

select c.CustomerID, City, ca.OrderID from Customers as c
cross apply
(select top 2 OrderID, CustomerID from Orders as o
where c.CustomerID = o.CustomerID order by OrderID desc) as ca

转载于:https://www.cnblogs.com/yanbinboy/archive/2009/05/01/1447522.html