SQL Server 2005 “Apply” 使用一例（每组查询n条记录）

Apply 是非标准 命令

这个例子可以用来在分组后，对于每一组查出相应的n条记录。

create table A
(Id int);

insert into A
select 1
union select 2
union select 3;

create table B
(Id int,
Date datetime);

insert into B
select 1, '2010-10-1' union
select 1, '2010-10-2' union
select 1, '2010-10-3' union
select 1, '2010-10-4' union
select 2, '2010-10-1' union
select 2, '2010-10-2' union
select 2, '2010-10-9';

select A.Id, B.Date
from A cross apply 
(select top 2 Id, Date from B where B.Id = A.Id order by Date desc) as B;

select A.Id, B.Date
from A outer apply 
(select top 2 Id, Date from B where B.Id = A.Id order by Date desc) as B;

drop table A;
drop table B;

查询结果1：

查询结果2：

注意cross apply与outer apply的区别。