select a.* from a left join b where a.CLXXBH=b.CLXXBH union all select b.* from b left join a where a.CLXXBH<>b.CLXXBH
select isnull(b.CLXXBH,a.CLXXBH) CLXXBH,isnull(b.c1,a.c1) c1,isnull(b.c2,a.c2) c2 ... from a left join b on a.CLXXBH = b.CLXXBH
不对吧,a.CLXXBH<>b.CLXXBH查出来是多少条?
select b.* from a inner join b where a.CLXXBH=b.CLXXBH union all select a.* from a where not exists(select 1 from b where a.CLXXBH=b.CLXXBH)
if exists(select 1 from a,b where a.CLXXBH=b.CLXXBH) select * from b else select * from a
select a.* from a where exists (select 1 from b where a.CLXXBH=b.CLXXBH) union all select b.* from b where not exists (select 1 from a where a.CLXXBH=b.CLXXBH)(select 1 from a where a.CLXXBH=b.CLXXBH)
select a.* from a where exists (select 1 from b where a.CLXXBH=b.CLXXBH) union all select b.* from b where not exists (select 1 from a where a.CLXXBH=b.CLXXBH
select a.* from a where exists (select 1 from b where a.CLXXBH=b.CLXXBH) union all select b.* from b where not exists (select 1 from a where a.CLXXBH=b.CLXXBH)
create table a ( ID int, name varchar(50), CLXXBH varchar(50) ) create table b ( ID int, name varchar(50), CLXXBH varchar(50) ) delete from a where id=1 insert into a(id,name,CLXXBH)values(1,'a',1) insert into a(id,name,CLXXBH)values(1,'a',2) insert into a(id,name,CLXXBH)values(1,'a',3) insert into a(id,name,CLXXBH)values(1,'a',4) insert into a(id,name,CLXXBH)values(1,'a',5) insert into a(id,name,CLXXBH)values(1,'a',6) insert into a(id,name,CLXXBH)values(1,'a',7)insert into b(id,name,CLXXBH)values(2,'b',11) insert into b(id,name,CLXXBH)values(2,'b',10) insert into b(id,name,CLXXBH)values(2,'b',9) insert into b(id,name,CLXXBH)values(2,'b',8) insert into b(id,name,CLXXBH)values(2,'b',5) insert into b(id,name,CLXXBH)values(2,'b',6) insert into b(id,name,CLXXBH)values(2,'b',7)select b.id,b.name,b.CLXXBH from a,b where a.CLXXBH=b.CLXXBH union select a.id,a.name,a.CLXXBH from a where a.CLXXBH not in(select b.CLXXBH from b)
union all
select b.* from b left join a where a.CLXXBH<>b.CLXXBH
select isnull(b.CLXXBH,a.CLXXBH) CLXXBH,isnull(b.c1,a.c1) c1,isnull(b.c2,a.c2) c2 ...
from a left join b on a.CLXXBH = b.CLXXBH
union all
select a.* from a where not exists(select 1 from b where a.CLXXBH=b.CLXXBH)
select * from b
else
select * from a
where exists (select 1 from b where a.CLXXBH=b.CLXXBH)
union all
select b.* from b
where not exists (select 1 from a where a.CLXXBH=b.CLXXBH)(select 1 from a where a.CLXXBH=b.CLXXBH)
where exists (select 1 from b where a.CLXXBH=b.CLXXBH)
union all
select b.* from b
where not exists (select 1 from a where a.CLXXBH=b.CLXXBH
where exists (select 1 from b where a.CLXXBH=b.CLXXBH)
union all
select b.* from b
where not exists (select 1 from a where a.CLXXBH=b.CLXXBH)
create table a
(
ID int,
name varchar(50),
CLXXBH varchar(50)
)
create table b
(
ID int,
name varchar(50),
CLXXBH varchar(50)
)
delete from a where id=1
insert into a(id,name,CLXXBH)values(1,'a',1)
insert into a(id,name,CLXXBH)values(1,'a',2)
insert into a(id,name,CLXXBH)values(1,'a',3)
insert into a(id,name,CLXXBH)values(1,'a',4)
insert into a(id,name,CLXXBH)values(1,'a',5)
insert into a(id,name,CLXXBH)values(1,'a',6)
insert into a(id,name,CLXXBH)values(1,'a',7)insert into b(id,name,CLXXBH)values(2,'b',11)
insert into b(id,name,CLXXBH)values(2,'b',10)
insert into b(id,name,CLXXBH)values(2,'b',9)
insert into b(id,name,CLXXBH)values(2,'b',8)
insert into b(id,name,CLXXBH)values(2,'b',5)
insert into b(id,name,CLXXBH)values(2,'b',6)
insert into b(id,name,CLXXBH)values(2,'b',7)select b.id,b.name,b.CLXXBH from a,b where a.CLXXBH=b.CLXXBH
union
select a.id,a.name,a.CLXXBH from a where a.CLXXBH not in(select b.CLXXBH from b)