declare @T1 Table(id int,c1 varchar(10))
declare @T2 Table(id int,c1 varchar(10),T1Id int,OrderNo int)insert into @T1(id,c1)
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'aa' insert into @T2(Id,c1,T1Id,OrderNo)
select 1,'ddd2',1,1 union all
select 2,'ddd3',2,1 union all
select 3,'ddd4',2,2 union all
select 4,'ddd5',3,1 union all
select 5,'ddd6',3,2 union all
select 6,'ddd7',3,3 select * from @T1
select * from @T2/*
想要的结果是:
T1.Id T2.Id T2.c1
1 1 'ddd2'
2 3 'ddd4'
3 6 'ddd7'
4 null null
*/
测试sqlselect
FROM B LEFT JOIN A ON xxxx
/*
想要的结果是:
T1.Id T2.Id T2.c1
1 1 'ddd2'
2 3 'ddd4'
3 6 'ddd7'
4 null null
*/
select a.id,b.id,b.c1 from @T1 a
left join
(
select a.* from @T2 a
inner join
(
select T1Id,MAX(OrderNo) as orderNo from @T2 group by T1Id
) b on a.T1Id = b.T1Id and a.OrderNo=b.orderNo
) b on a.id = b.T1Id我知道一种方法,就是这个,我觉得应该有更好的方法.请求帮助.
declare @T1 Table(id int,c1 varchar(10))
declare @T2 Table(id int,c1 varchar(10),T1Id int,OrderNo int)
insert into @T1(id,c1)
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'aa'
insert into @T2(Id,c1,T1Id,OrderNo)
select 1,'ddd2',1,1 union all
select 2,'ddd3',2,1 union all
select 3,'ddd4',2,2 union all
select 4,'ddd5',3,1 union all
select 5,'ddd6',3,2 union all
select 6,'ddd7',3,3
select a.id 't1id',b.id 't2id',b.c1 't2c1'
from @T1 a
left join
(select id,c1,T1Id,
row_number() over(partition by T1Id order by id desc) 'rn'
from @T2) b on a.id=b.T1Id and b.rn=1
/*
t1id t2id t2c1
----------- ----------- ----------
1 1 ddd2
2 3 ddd4
3 6 ddd7
4 NULL NULL(4 row(s) affected)
*/
SELECT a.Id,b.Id,b.c1
FROM @T1 a
OUTER APPLY
(
SELECT TOP 1 Id,c1 FROM @T2 WHERE T1Id=a.id ORDER BY id desc
) b
create Table #T2 (id int,c1 varchar(10),T1Id int,OrderNo int)insert into #T1(id,c1)
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'aa' insert into #T2(Id,c1,T1Id,OrderNo)
select 1,'ddd2',1,1 union all
select 2,'ddd3',2,1 union all
select 3,'ddd4',2,2 union all
select 4,'ddd5',3,1 union all
select 5,'ddd6',3,2 union all
select 6,'ddd7',3,3 select * from #T1
select * from #T2
select a.Id,b.Id,b.c1 from #T1 a
left join
(
select ROW_NUMBER()over(partition by t1id order by id desc)num,* from #T2
) b on a.id=b.T1Id and b.num=1
-------------------------------------------------------------------
Id Id c1
----------- ----------- ----------
1 1 ddd2
2 3 ddd4
3 6 ddd7
4 NULL NULL(4 行受影响)
declare @T2 Table(id int,c1 varchar(10),T1Id int,OrderNo int)
insert into @T1(id,c1)
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'aa'
insert into @T2(Id,c1,T1Id,OrderNo)
select 1,'ddd2',1,1 union all
select 2,'ddd3',2,1 union all
select 3,'ddd4',2,2 union all
select 4,'ddd5',3,1 union all
select 5,'ddd6',3,2 union all
select 6,'ddd7',3,3
select t1.id,
(select top 1 t2.ID from @T2 t2
where t1.id = t2.T1Id order by OrderNo) as 't2.id',
(select top 1 t2.c1 from @T2 t2
where t1.id = t2.T1Id order by OrderNo) as 't2.c1'
from @T1 t1
/*
id t2.id t2.c1
1 1 ddd2
2 2 ddd3
3 4 ddd5
4 NULL NULL
*/