declare @t table(id int, type int)
insert @t select 1, 2
union all select
2 , 2
union all select
3 , 2
union all select
4 , 3
union all select
4 , 3
declare @t1 table(s int identity(1,1),id int, type int)
declare @t2 table(s int identity(1,1),id int, type int)
insert @t1 select * from @t where type=2
insert @t2 select * from @t where type=3
select t1.id,t1.type,t2.id as id2,t2.type as type2
from @t1 t1 left outer join @t2 t2 on t1.s=t2.s
(所影响的行数为 2 行)
id type id2 type2
----------- ----------- ----------- -----------
1 2 4 3
2 2 4 3
3 2 NULL NULL