有两个表:
A:
AID ShuLiang1
1 12
2 30
5 8B:
AID ShuLiang2
1 5
3 9要求查询结果:
AID ShuLiang1 ShuLiang2
1 12 5
2 30
3 9
5 8由于数据库数据较多,求效率高的语句,谢谢
A:
AID ShuLiang1
1 12
2 30
5 8B:
AID ShuLiang2
1 5
3 9要求查询结果:
AID ShuLiang1 ShuLiang2
1 12 5
2 30
3 9
5 8由于数据库数据较多,求效率高的语句,谢谢
from A a
full join B b on a.AID = b.AID
a
id , value
1 , 11
2 , 21
3 , 31
4 , 41
5 , 51b
id , value
1 , 12
2 , 22
3 , 32
4 , 42c , value
1 , 13
2 , 23
3 , 33
5 , 53d
a , value1 , value2 , value3
1 , 11 , 12 , 13
2 , 21 , 22 , 23
3 , 31 , 32 , 33
4 , 41 , 42 , 0
5 , 51 , 0 , 53
CREATE TABLE #a ([id] [char] (10),[value] [int])
CREATE TABLE #b ([id] [char] (10),[value] [int])
CREATE TABLE #c ([id] [char] (10),[value] [int])insert into #a(id,value) values('1',11)
insert into #a(id,value) values('2',21)
insert into #a(id,value) values('3',31)
insert into #a(id,value) values('4',41)
insert into #a(id,value) values('5',51) insert into #b(id,value) values('1',12)
insert into #b(id,value) values('2',22)
insert into #b(id,value) values('3',32)
insert into #b(id,value) values('4',42) insert into #c(id,value) values('1',13)
insert into #c(id,value) values('2',23)
insert into #c(id,value) values('3',33)
insert into #c(id,value) values('5',53) select isnull(isnull(#a.id,#b.id),#c.id) id,#a.value value1,#b.value value2,#c.value value3
from #a full join #b on #a.id=#b.id
full join #c on isnull(#a.id,#b.id)=#c.iddrop table #a
drop table #b
drop table #cid value1 value2 value3
---------- ----------- ----------- -----------
1 11 12 13
2 21 22 23
3 31 32 33
4 41 42 NULL
5 51 NULL 53(所影响的行数为 5 行)2个表,
表A
a1 a2
1 9
2 10
3 67
4 33
表B
b1 b2
2 31
4 99
想实现这样
a1 a2 b2
1 9 NULL(或0或不体现任何数)
2 10 31
3 67 NULL(或0或不体现任何数)
4 33 99
谢谢各位指导~
CREATE TABLE #a ([a1] [char] (10),[a2] [int])
CREATE TABLE #b ([b1] [char] (10),[b2] [int])insert into #a(a1,a2) values('1',9)
insert into #a(a1,a2) values('2',10)
insert into #a(a1,a2) values('3',67)
insert into #a(a1,a2) values('4',33) insert into #b(b1,b2) values('2',31)
insert into #b(b1,b2) values('4',99) --显示NULL
--select isnull(#a.a1,#b.b1) a1,#a.a2 a2,#b.b2 b2
--from #a full join #b on #a.a1=#b.b1--显示0
select #A.a1, #A.a2, isnull(#B.b2, 0)
from #A left join #B on #A.a1 = #B.b1drop table #a
drop table #b
select isnull(isnull(a.aid,b.aid),c.aid) as aid,shuliang1,shuliang2 from a full join b on a.aid=b.aid full join c on c.aid=b.aid
insert into @a select 1,12 union all
select 2,30 union all
select 5,8
declare @b table (AID int, ShuLiang2 int)
insert into @b select 1,5 union all
select 3,9
select isnull(a.AID,b.AID) as AID,a.ShuLiang1,b.ShuLiang2 from @a a full join @b b on a.AID=b.AID
结果:
AID ShuLiang1 ShuLiang2
----------- ----------- -----------
1 12 5
3 NULL 9
5 8 NULL
2 30 NULL(所影响的行数为 4 行)
SELECT AID,ShuLiang1, '' AS ShuLiang2
FROM A
UNION ALL
SELECT AID, '' AS ShuLiang1,ShuLiang2
FROM B--可以用完全外连接
SELECT ISNULL(A.AID,B.AID) AS AID,
ISNULL(A.ShuLiang1, '') AS ShuLiang1,
ISNULL(B.ShuLiang2, '') AS ShuLiang2
FROM A FULL OUTER JOIN B
ON A.AID=B.AID