outer apply 、cross apply

创建表

create table #a_aa_a(
t_id int,
t_name varchar(10),
)
insert into #a_aa_a VALUES  (1,'盲僧')
insert into #a_aa_a VALUES  (2,'亚索')
insert into #a_aa_a VALUES  (3,'盖伦')


create table #b_bb_b(
t_id int,
t_name varchar(10),
t_date int,
t_grade int
)
insert into #b_bb_b VALUES  (1,'盲僧',20210401,1)
insert into #b_bb_b VALUES  (1,'盲僧',20210403,2)
insert into #b_bb_b VALUES  (1,'盲僧',20210405,3)
insert into #b_bb_b VALUES  (2,'亚索',20210401,1)
insert into #b_bb_b VALUES  (2,'亚索',20210403,2)
insert into #b_bb_b VALUES  (2,'亚索',20210405,3)

结果

 CROSS APPLY：关联后取top前几个满足排序后的数据

select * from #a_aa_a t1 
CROSS APPLY
(select top 2 * from #b_bb_b t where t1.t_id=t.t_id order by t.t_grade) t2 

结果：

 outer apply ：同理，但是以左边（外面）的表为主表进行外关联--可以看为是left join

select * from #a_aa_a t1 
outer apply 
(select top 2* from #b_bb_b t where t1.t_id=t.t_id order by t.t_grade) t2