select a.C1,a.C2,C3,C4 from (select C1,C2 from 表1 union all select C1,C2 from 表2)a left join 表1 b on a.C1=b.C1 and a.C2=b.C2 left join 表2 c on a.C1=c.C1 and a.C2=c.C2
select c1,c2,c3,c4=null from t1 union all select c1,c2,c3,c4 from t2主要是两的字段数一样多,数据类型也一样
select a.C1,a.C2,C3,C4 from (select distinct C1,C2 from 表1 union all select C1,C2 from 表2)a left join 表1 b on a.C1=b.C1 and a.C2=b.C2 left join 表2 c on a.C1=c.C1 and a.C2=c.C2 在一楼的基础上修改了一下
select isnull(a.C1,b.c1) as c1,isnull(a.c2,b.c2),a.c3,b.c4 from taba a full join tabb b on a.c2=b.c2 试下这个。
还是不对。。 select a.C1,a.C2,C3,C4 from (select C1,C2 from 表1 union select C1,C2 from 表2)a left join 表1 b on a.C1=b.C1 and a.C2=b.C2 left join 表2 c on a.C1=c.C1 and a.C2=c.C2 再试试这个
楼猪,还有一种做法就是建立derived table,我用SQL SERVER 做出了你要的结果: SELECT derivedtbl_1.C1, derivedtbl_1.C2, Table_1.C3, Table_2.C4 FROM (SELECT C1, C2 FROM Table_1 AS Table_1_1 UNION SELECT C1, C2 FROM Table_2 AS Table_2_1) AS derivedtbl_1 LEFT OUTER JOIN Table_1 ON derivedtbl_1.C2 = Table_1.C2 LEFT OUTER JOIN Table_2 ON derivedtbl_1.C2 = Table_2.C2首先把两个table用UNION(注意不要用UNION ALL)得到一个derived table, 然后用这个derived table对table1 和table2 做left join.
create table T1 (c1 nchar(1000), c2 int, c3 int) create table T2 (c1 nchar(1000), c2 int, c4 int) insert into T1 values('AAA',1, 23) insert into T1 values('AAA',2, 32) insert into T1 values('AAA',3, 33) insert into T1 values('AAA',4, 44) insert into T2 values('AAA',4, 23) insert into T2 values('AAA',2, 32) insert into T2 values('AAA',3, 33) insert into T2 values('AAA',5, 44) insert into T2 values('AAA',6, 23) 以上是建表代码,以下是查询语句select t.c1, t.c2, T1.c3, 0, T2.c4 from ( select distinct a.c2,a.c1 from (select c1, c2 from T1 union all select c1, c2 from T2)a )t left join T1 on t.c2=T1.c2 left join T2 on t.c2=T2.c2
哦。第一行的那个0不要。原来那个isnull被我去掉了,0没消掉select t.c1, t.c2, T1.c3, T2.c4 from ( select distinct a.c2,a.c1 from (select c1, c2 from T1 union all select c1, c2 from T2)a )t left join T1 on t.c2=T1.c2 left join T2 on t.c2=T2.c2
from (select C1,C2 from 表1 union all select C1,C2 from 表2)a
left join 表1 b on a.C1=b.C1 and a.C2=b.C2
left join 表2 c on a.C1=c.C1 and a.C2=c.C2
union all
select c1,c2,c3,c4 from t2主要是两的字段数一样多,数据类型也一样
select a.C1,a.C2,C3,C4
from (select distinct C1,C2 from 表1 union all select C1,C2 from 表2)a
left join 表1 b on a.C1=b.C1 and a.C2=b.C2
left join 表2 c on a.C1=c.C1 and a.C2=c.C2 在一楼的基础上修改了一下
select isnull(a.C1,b.c1) as c1,isnull(a.c2,b.c2),a.c3,b.c4
from taba a full join tabb b on a.c2=b.c2
试下这个。
select a.C1,a.C2,C3,C4
from (select C1,C2 from 表1 union select C1,C2 from 表2)a
left join 表1 b on a.C1=b.C1 and a.C2=b.C2
left join 表2 c on a.C1=c.C1 and a.C2=c.C2 再试试这个
SELECT derivedtbl_1.C1, derivedtbl_1.C2, Table_1.C3, Table_2.C4
FROM (SELECT C1, C2
FROM Table_1 AS Table_1_1
UNION
SELECT C1, C2
FROM Table_2 AS Table_2_1) AS derivedtbl_1 LEFT OUTER JOIN
Table_1 ON derivedtbl_1.C2 = Table_1.C2 LEFT OUTER JOIN
Table_2 ON derivedtbl_1.C2 = Table_2.C2首先把两个table用UNION(注意不要用UNION ALL)得到一个derived table, 然后用这个derived table对table1 和table2 做left join.
create table T2 (c1 nchar(1000), c2 int, c4 int)
insert into T1 values('AAA',1, 23)
insert into T1 values('AAA',2, 32)
insert into T1 values('AAA',3, 33)
insert into T1 values('AAA',4, 44)
insert into T2 values('AAA',4, 23)
insert into T2 values('AAA',2, 32)
insert into T2 values('AAA',3, 33)
insert into T2 values('AAA',5, 44)
insert into T2 values('AAA',6, 23)
以上是建表代码,以下是查询语句select t.c1, t.c2, T1.c3, 0, T2.c4
from (
select distinct a.c2,a.c1
from (select c1, c2 from T1 union all select c1, c2 from T2)a
)t left join T1 on t.c2=T1.c2
left join T2 on t.c2=T2.c2
from (
select distinct a.c2,a.c1
from (select c1, c2 from T1 union all select c1, c2 from T2)a
)t left join T1 on t.c2=T1.c2
left join T2 on t.c2=T2.c2