select * from a表 as a Full Outer Join b表 as b on a.id=b.id
解决方案 »
- 统计库存如何可以合并以下两条SQL语句?
- 100多万条记录的表 使用如下查询 速度极慢 怎么办
- 数据库搜索字符和数字的效率一样吗?
- 这样一个过程,如何用函数实现啊!
- 救助:SQL两个表之间的加减取值
- 问个问题
- 刚学习SQL SERVER,不知道怎么在主表中的“自动增加序号列”插入的时候在重表也自动插入
- 在线等,我是新手.一行变两行的办法在哪里?
- 痛苦啊,俺的win2k一启动到进度条将要结束就马上出现:Stop:c000026c unkonw hard error!!
- 请问 一句SQL ,加入一个其它表的字段
- SQL Server 能否在程序中自动安装好?
- 求救!!哪儿有基于值的数据库查询优化的资料!!急!!!!!!1
就可以是向你想要的结果
full outer join a表 as a
on c.id=a.id
Full Outer Join b表 as b
on c.id=b.id c表为人员表
id name
1 a
2 b
3 c
4 d
5 e
from a表 as a left Outer Join b表 as b on a.id=b.id
union
select b.id as id,b.name as name,null as sex,b.book as boook
from a表 as a right Outer Join b表 as b on a.id<>b.id
go
insert #a values(1,'a', 1)
insert #a values(2,'b', 0)
insert #a values(3,'c', 1)
insert #a values(4,'d', 0)create table #b(id int, name char(2),book varchar(100))
insert #b values(1,'a','adsfoaiu')
insert #b values(2,'b','ofuadsou')
insert #b values(3,'c','oiuou')
insert #b values(5,'e','ODUFPOAU')
go
/*欲查询出如下结果:
id name sex book
1 a 1 adsfoaiu
2 b 0 ofuadsou
3 c 1 oiuou
4 d 0 null
5 e null ODUFPOAU
*/
select id,max(name) as name ,max(sex) as sex ,max(book) as book from (
select a.id as id,a.name as name,a.sex as sex,null as book
from #a as a left outer Join #b as b on a.id=b.id
union
select b.id as id,b.name as name,null as sex,b.book as book
from #a as c right outer Join #b as b on c.id=b.id )as d
group by id
create table testM(
ID int,
name varchar(8),
sex varchar(1)
primary key(ID)
)
go
create table testD(
ID int,
name varchar(8),
book varchar(100)
primary key(ID)
)
go--建资料
insert into testM values(1,'a','1')
insert into testM values(2,'b','0')
insert into testM values(3,'c','1')
insert into testM values(4,'d','0')insert into testD values(1,'a','adsfoaiu')
insert into testD values(2,'b','ofuadsou')
insert into testD values(3,'c','oiuou')
insert into testD values(5,'e','ODUFPOAU')--我查
select M1.ID,M1.name,M1.sex,D1.book
from testM M1
left join testD D1 on M1.ID=D1.ID
union all
select D1.ID,D1.name,M1.sex,D1.book
from testD D1
left join testM M1 on M1.ID=D1.ID
select M1.ID,M1.name,M1.sex,D1.book
from testM M1
left join testD D1 on M1.ID=D1.ID
union
select D1.ID,D1.name,M1.sex,D1.book
from testD D1
left join testM M1 on M1.ID=D1.ID