select a.*,b.字段3,c.字段3
from table1 a left join table2 b on a.字段1=b.字段1 and a.字段2=b.字段2
left join table3 c on a.字段1=c.字段1 and a.字段2=c.字段2
from table1 a left join table2 b on a.字段1=b.字段1 and a.字段2=b.字段2
left join table3 c on a.字段1=c.字段1 and a.字段2=c.字段2
表1:
字段1 字段2 字段3
a b 5
表2:
字段1 字段2 字段3
a b 3
d b 5
表3:
字段1 字段2 字段3
a b 2
d b 4
上面的查询就不能得到这种结果了
字段1 字段2 表1.字段3 表2.字段3 表3.字段3
a b 5 3 2
d b 0 5 4
declare @a table (col1 nchar(2),col2 nchar(2),col3 int)
declare @b table (col1 nchar(2),col2 nchar(2),col3 int)
declare @c table (col1 nchar(2),col2 nchar(2),col3 int)
insert into @a
select 'a','b',5insert into @b
select 'a','b',3
union
select 'd','b',5insert into @c
select 'a','b',2
union
select 'd','b',4select a.*,isnull(b.col3,0),isnull(c.col3,0),isnull(d.col3,0) from
(
select col1,col2 from @a
union
select col1,col2 from @b
union
select col1,col2 from @c) a
left join @a b on a.col1=b.col1 and a.col2=b.col2
left join @b c on a.col1=c.col1 and a.col2=c.col2
left join @c d on a.col1=d.col1 and a.col2=d.col2
select t.*,a.qty qty1,b.qty qty2,c.qty qty3 from
(select * from
(select rawt,rawn from a
union all
select rawt,rawn from b
union all
select rawt,rawn from c) temp
group by rawt,rawn) t
left join a on t.rawt=a.rawt and t.rawt=a.rawt
left join b on t.rawt=b.rawt and t.rawt=b.rawt
left join c on t.rawt=c.rawt and t.rawt=c.rawt
case when b.字段3 is NULL then 0 else b.字段3 end as 字段3
case when c.字段3 is NULL then 0 else c.字段3 end as 字段3from 表1 a left join 表2 b on a.字段1=b.字段1 and a.字段2=b.字段2
left join 表3 c on a.字段1=c.字段1 and a.字段2=c.字段2
select t.*,a.qty qty1,b.qty qty2,c.qty qty3 from
(select * from
(select rawt,rawn from a
union all
select rawt,rawn from b
union all
select rawt,rawn from c) temp
group by rawt,rawn) t
left join a on t.rawt=a.rawt and t.rawn=a.rawn
left join b on t.rawt=b.rawt and t.rawn=b.rawn
left join c on t.rawt=c.rawt and t.rawn=c.rawn
from [表1] a left join [表2] b on a.字段1=b.字段1 and a.字段2=b.字段2
left join [表3] c on a.字段1=c.字段1 and a.字段2=c.字段2