select a.*,isnull(t.usage , '') usage from a
left join
(
select * from b
union all
select * from c
union all
select * from d
) t
on a.id = t.id
2、假设b,c,d中各有一条记录是对应A的。
select a.*,isnull(b.usage,'') busage,isnull(c.usage,'') cusage,isnull(d.usage,'') dusage from a
left join b on a.id = b.id
left join c on a.id = c.id
left join d on a.id = d.id
select a.id, a.name, a.class, t.usage
from @a as a
left join
(
select id,usage from @b
union all
select id,usage from @c
union all
select id,usage from @d
) as t on a.id=t.id
select
*,
[usage]=isnull(isnull((select top 1 [usage] from @b where ID=a.ID),
(select top 1 [usage] from @c where ID=a.ID)),
(select top 1 [usage] from @d where ID=a.ID))
from
@a a
(所影响的行数为 3 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
ID Name usage
----------- ----- -----
1 a aa
2 b bb
3 c cc
select a.id, a.name, a.class, t.usage
from A
left join
(
select id,usage from b
union all
select id,usage from c
union all
select id,usage from d
) as t on a.id=t.id