表结果如下:表A:
ID FiledOne
1 A
2 B
表B:
ID FiledTwo
1 B
3 C
4 D
要求根据ID合并表A跟表B的字段,没有的以NULL填充。结果如下:
ID FieldOne FieldTwo
1 A B
2 B NULL
3 NULL C
4 NULL D
请高手帮忙解决,不甚感激
ID FiledOne
1 A
2 B
表B:
ID FiledTwo
1 B
3 C
4 D
要求根据ID合并表A跟表B的字段,没有的以NULL填充。结果如下:
ID FieldOne FieldTwo
1 A B
2 B NULL
3 NULL C
4 NULL D
请高手帮忙解决,不甚感激
FieldOne,
Fieldtwo
from ta a full join tb b
on a.id=b.id
go
create table A
(
ID int,
FiledOne varchar(10)
)
insert into A select 1,'A'
union all select 2,'B'
goif object_id('B') is not null drop table B
go
create table B
(
ID int,
FiledTwo varchar(10)
)
insert into B select 1,'B'
union all select 3,'C'
union all select 4,'D'
goselect isnull(A.ID,B.ID) ID,A.FiledOne,B.FiledTwo
from A
full join B
on A.ID=b.IDID FiledOne FiledTwo
----------- ---------- ----------
1 A B
2 B NULL
3 NULL C
4 NULL D(4 行受影响)
drop table ta
Go
Create table ta([ID] int,[FiledOne] nvarchar(1))
Insert ta
select 1,N'A' union all
select 2,N'B'
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[FiledTwo] nvarchar(1))
Insert tb
select 1,N'B' union all
select 3,N'C' union all
select 4,N'D'
Go
select isnull(a.id,b.id)id,
a.[FiledOne],
b.[FiledTwo]
from ta a full join tb b
on a.id=b.id
/*
id FiledOne FiledTwo
----------- -------- --------
1 A B
2 B NULL
3 NULL C
4 NULL D*/
如果A.ID为空的话
就去B.ID的值