select isnull(isnull(#A.a,#B.b),#c.c) name,max(a2) a2,max(b2) b2,max(#C.c2) c2
from #A
full join #B on #A.a=#B.b
full join #C on #A.a=#C.c
group by isnull(isnull(#A.a,#B.b),#c.c)
order by isnull(isnull(#A.a,#B.b),#c.c)
from #A
full join #B on #A.a=#B.b
full join #C on #A.a=#C.c
group by isnull(isnull(#A.a,#B.b),#c.c)
order by isnull(isnull(#A.a,#B.b),#c.c)
---- ---- ---- ----
a 1 NULL NULL
b NULL 2 3
c NULL 3 NULL
d NULL NULL 4(所影响的行数为 4 行)
from(
select a name,a2,null b2,null c2 from #a
union all
select b,null,b2,null from #b
union all
select c,null,null,c2 from #c
) a group by name
name a2 b2 c2
---- ---- ---- ----
a 1 NULL NULL
b NULL 2 3
c NULL 3 NULL
d NULL NULL 4(所影响的行数为 4 行)
字段有可能是字符串不是问题,goup by 对于这个也适用,你用max就行了.select isnull(isnull(#A.a,#B.b),#c.c) name,max(a2) a2,max(b2) b2,max(#C.c2) c2
from #A
full join #B on #A.a=#B.b
full join #C on #A.a=#C.c
group by isnull(isnull(#A.a,#B.b),#c.c)
order by isnull(isnull(#A.a,#B.b),#c.c)
from(
select a name,a2,null b2,null c2 from #a
union all
select b,null,b2,null from #b
union all
select c,null,null,c2 from #c
) a group by name