select isnull(表1.字段a,表2.字段a) 字段a, isnull(cast(表1.字段b as varchar) , '') 字段b, isnull(cast(表2.字段c as varchar) , '') 字段c from 表1 full join 表2 on 表1.字段a = 表2.字段a
SELECT ISNULL(A.字段a,B.字段a) AS 字段a, ISNULL(RTRIM(A.字段b),'') AS 字段b, ISNULL(RTRIM(B.字段c),'') AS 字段c FROM tb1 AS A FULL JOIN tb2 AS B ON A.字段a=B.字段a
---测试数据--- if object_id('[表1]') is not null drop table [表1] go create table [表1]([字段a] varchar(1),[字段b] int) insert [表1] select 'A',30 union all select 'B',50 union all select 'C',20 if object_id('[表2]') is not null drop table [表2] go create table [表2]([字段a] varchar(1),[字段c] int) insert [表2] select 'A',50 union all select 'E',10
---查询--- select isnull(a.[字段a],b.[字段a]) as [字段a], isnull(ltrim(a.[字段b]),'') as [字段b], isnull(ltrim(b.[字段c]),'') as [字段c] from [表1] a full join [表2] b on a.[字段a]=b.[字段a]---结果--- 字段a 字段b 字段c ---- ------------ ------------ A 30 50 B 50 C 20 E 10(所影响的行数为 4 行)
create table 表1(字段a varchar(10) , 字段b varchar(10) ) insert into 表1 values('A' , 30 ) insert into 表1 values('B' , 50 ) insert into 表1 values('C' , 20 ) create table 表2(字段a varchar(10) , 字段c varchar(10) ) insert into 表2 values('A' , 50 ) insert into 表2 values('E' , 10 ) goselect isnull(表1.字段a,表2.字段a) 字段a, isnull(cast(表1.字段b as varchar) , '') 字段b, isnull(cast(表2.字段c as varchar) , '') 字段c from 表1 full join 表2 on 表1.字段a = 表2.字段adrop table 表1 , 表2/* 字段a 字段b 字段c ---------- ------------------------------ ------------------------------ A 30 50 B 50 C 20 E 10(所影响的行数为 4 行)*/
select a.字段A,a.字段B,b.字段C from 表1 a full outer join 表2 on a.字段A = b.字段A
isnull(cast(表1.字段b as varchar) , '') 字段b,
isnull(cast(表2.字段c as varchar) , '') 字段c
from 表1 full join 表2 on 表1.字段a = 表2.字段a
ISNULL(A.字段a,B.字段a) AS 字段a,
ISNULL(RTRIM(A.字段b),'') AS 字段b,
ISNULL(RTRIM(B.字段c),'') AS 字段c
FROM
tb1 AS A
FULL JOIN
tb2 AS B
ON A.字段a=B.字段a
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([字段a] varchar(1),[字段b] int)
insert [表1]
select 'A',30 union all
select 'B',50 union all
select 'C',20
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([字段a] varchar(1),[字段c] int)
insert [表2]
select 'A',50 union all
select 'E',10
---查询---
select
isnull(a.[字段a],b.[字段a]) as [字段a],
isnull(ltrim(a.[字段b]),'') as [字段b],
isnull(ltrim(b.[字段c]),'') as [字段c]
from [表1] a
full join [表2] b
on a.[字段a]=b.[字段a]---结果---
字段a 字段b 字段c
---- ------------ ------------
A 30 50
B 50
C 20
E 10(所影响的行数为 4 行)
insert into 表1 values('A' , 30 )
insert into 表1 values('B' , 50 )
insert into 表1 values('C' , 20 )
create table 表2(字段a varchar(10) , 字段c varchar(10) )
insert into 表2 values('A' , 50 )
insert into 表2 values('E' , 10 )
goselect isnull(表1.字段a,表2.字段a) 字段a,
isnull(cast(表1.字段b as varchar) , '') 字段b,
isnull(cast(表2.字段c as varchar) , '') 字段c
from 表1 full join 表2 on 表1.字段a = 表2.字段adrop table 表1 , 表2/*
字段a 字段b 字段c
---------- ------------------------------ ------------------------------
A 30 50
B 50
C 20
E 10(所影响的行数为 4 行)*/
select a.字段A,a.字段B,b.字段C
from 表1 a full outer join 表2
on a.字段A = b.字段A