join 的 用法

Question

declare @tab table([id] int,[m] int,[name] varchar(10),[num] int) 
Insert @tab 
select 1, 1, 'A', 10 union
select 2, 1, 'B', 20 union
select 3, 1, 'C', 30 union
select 1, 2, 'A', 11 union
select 2, 2, 'B', 21 union
select 4, 2, 'D', 31select t1.id,t1.name,t1.num,t2.name,t2.num
from @tab t1 left join @tab t2 on t2.[id]=t1.[id] and t2.m=2 
where t1.m=1
/* 结果：
id   name   num   name   num
1    A      10    A      11
2    B      20    B      21
3    C      30    NULL   NULL     
*//* 想要的结果：
id   name   num   name   num
1    A      10    A      11
2    B      20    B      21
3    C      30    NULL   NULL 
4    NULL   NULL  D      31    
*/
--用下列语句可以实现：
select t1.id,t1.name,t1.num,t2.name,t2.num
from @tab t1 left join @tab t2 on t2.[id]=t1.[id] and t2.m=2 
where t1.m=1
union
select 
ID, null, null, name, num 
from @tab
where m=2 and not (ID in (select ID from @tab where m=1))有没有更简单有效的办法？用 union 觉得有点别扭

Answer 1

select isnull(t1.id,t2.id),t1.name,t1.num,t2.name,t2.num
from @tab t1 
full join @tab t2 on t2.[id]=t1.[id] and t2.m=2 

Answer 2

declare @tab table([id] int,[m] int,[name] varchar(10),[num] int) 
Insert @tab 
select 1, 1, 'A', 10 union
select 2, 1, 'B', 20 union
select 3, 1, 'C', 30 union
select 1, 2, 'A', 11 union
select 2, 2, 'B', 21 union
select 4, 2, 'D', 31
 
select isnull(t1.id,t2.id) id,t1.name,t1.num,t2.name,t2.num
from (select * from @tab where m=1) t1  
full join (select * from @tab where m=2) t2 on t2.[id]=t1.[id] and t1.name=t2.name 
 
id          name       num         name       num         
----------- ---------- ----------- ---------- ----------- 
1           A          10          A          11
2           B          20          B          21
4           NULL       NULL        D          31
3           C          30          NULL       NULL（所影响的行数为 4 行）

Answer 3

declare @tab table([id] int,[m] int,[name] varchar(10),[num] int) 
Insert @tab 
select 1, 1, 'A', 10 union
select 2, 1, 'B', 20 union
select 3, 1, 'C', 30 union
select 1, 2, 'A', 11 union
select 2, 2, 'B', 21 union
select 4, 2, 'D', 31select isnull(t1.id,t2.id) id,t1.name,t1.num,t2.name,t2.num
from (select * from @tab where m=1) t1 
full join (select * from @tab where m=2) t2 
on t2.[id]=t1.[id] 
order by 1
/*
id          name       num         name       num
----------- ---------- ----------- ---------- -----------
1           A          10          A          11
2           B          20          B          21
3           C          30          NULL       NULL
4           NULL       NULL        D          31(4 行受影响)
*/

Answer 4

declare @tab table([id] int,[m] int,[name] varchar(10),[num] int) 
Insert @tab 
select 1, 1, 'A', 10 union
select 2, 1, 'B', 20 union
select 3, 1, 'C', 30 union
select 1, 2, 'A', 11 union
select 2, 2, 'B', 21 union
select 4, 2, 'D', 31--select t1.id,t1.name,t1.num,t2.name,t2.num
--from @tab t1 full join @tab t2 on t2.[id]=t1.[id] and t2.m=2 and t1.m=1select case when t1.id is not null then t1.id else t2.id end id,t1.name,t1.num,t2.name,t2.num
from (select * from @tab where m=1) t1 full join (select * from @tab where m=2) t2 on t1.id=t2.id
order by 1

Answer 5

select a1.[id],a2.[name],a2.[num],a3.[name],a3.[num]
from (select distinct [id] as [id] from @tab) a1
left join @tab a2 on a1.[id]=a2.[id] and a2.[m]=1
left join @tab a3 on a1.[id]=a3.[id] and a3.[m]=2