34,590
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: [TableA]
if object_id('[TableA]') is not null drop table [TableA]
create table [TableA] (PK_Col int,Col1 varchar(2),Col2 varchar(2),Col3 varchar(2))
--> 测试数据: [TableB]
if object_id('[TableB]') is not null drop table [TableB]
create table [TableB] (PK_Col int,Col1 varchar(2),Col2 varchar(2),Col3 varchar(2))
--> 测试数据: [TableC]
if object_id('[TableC]') is not null drop table [TableC]
create table [TableC] (PK_Col int,Col1 varchar(2),Col2 varchar(2),Col3 varchar(2))
insert into [TableC]
select 101,'x1','x2','x3' union all
select 102,'y1','y2','y3'
select pk_col=coalesce(a.pk_col,b.pk_col,c.pk_col),a.col1,a.col2,a.col3,b.col1,b.col2,b.col3,c.col1,c.col2,c.col3 from
tablea a full join tableb b on a.pk_col=b.pk_col
full join TableC c on isnull(a.pk_col,b.pk_col)=c.pk_col
--结果:
pk_col col1 col2 col3 col1 col2 col3 col1 col2 col3
----------- ---- ---- ---- ---- ---- ---- ---- ---- ----
101 NULL NULL NULL NULL NULL NULL x1 x2 x3
102 NULL NULL NULL NULL NULL NULL y1 y2 y3
--修改下:
select pk_col=coalesce(a.pk_col,b.pk_col,c.pk_col),a.col1,a.col2,a.col3,b.col1,b.col2,b.col3,c.col1,c.col2,c.col3 from
tablea a full join tableb b on a.pk_col=b.pk_col
full join TableC c on isnull(a.pk_col,b.pk_col)=c.pk_col
select pk_col=coalesce(a.pk_col,b.pk_col,c.pk_col),a.col1,a.col2,a.col3,b.col1,b.col2,b.col3,c.col1,c.col2,c.col3 from
tablea a full join tableb b on a.pk_col=b.pk_col
full join TableC c on a.pk_col=c.pk_col
看不懂楼主的结果了, 这样应该对把
SELECT
a.*, b.col1, b.col2, b.col3, c.col1, c.col2, c.col3
FROM a LEFT JOIN b on a.PK_Col=b.PK_Col
LEFT JOIN c on a.PK_Col=c.PK_Col
select
a.*,b.col1,b.col2,b.col3,c.col1,c.col2,c.col3
from
tablea a
left join tableb b on
a.pk_col=b.pk_col
left join TableC c on
a.pk_col=c.pk_col
select
a.*,
b.col1,
b.col2,
b.col3,
c.col1,
c.col2,
c.col3
from
a
left join b on a.PK_Col=b.PK_Col
left join c on a.PK_Col=c.PK_Col
--这样?
select a.*,b.col1,b.col2,b.col3,c.col1,c.col2,c.col3 from
tablea a left join tableb b on a.pk_col=b.pk_col
left join TableC c on a.pk_col=c.pk_col