有2个表:
表a
编号,姓名, 年龄
2, zhanggong, 20
3, w,
4, dali,
5, lidong, 25 表b 编号,姓名, 年龄
1, wangli,
2, zhanggong,30
3, xiaowu, 40
5, lidong,
要得到的结果:
编号,姓名, 年龄
1, wangli,
2, zhanggong,30
3, xiaowu, 40
4, dali,
5, lidong, 25
想用1条sql语句建一个视图,其中,
1.编号字段在每个表中唯一
2.如编号字段在两个表中重复,姓名字段有可能不同,则选择b表中字段。
3.年龄字段在两个表中可能重复也可能为空,如编号重复,年龄不同选b表中字段,如在两个表中有一个为空,选另一个表中不为空的值
请高手指点,先谢了。
表a
编号,姓名, 年龄
2, zhanggong, 20
3, w,
4, dali,
5, lidong, 25 表b 编号,姓名, 年龄
1, wangli,
2, zhanggong,30
3, xiaowu, 40
5, lidong,
要得到的结果:
编号,姓名, 年龄
1, wangli,
2, zhanggong,30
3, xiaowu, 40
4, dali,
5, lidong, 25
想用1条sql语句建一个视图,其中,
1.编号字段在每个表中唯一
2.如编号字段在两个表中重复,姓名字段有可能不同,则选择b表中字段。
3.年龄字段在两个表中可能重复也可能为空,如编号重复,年龄不同选b表中字段,如在两个表中有一个为空,选另一个表中不为空的值
请高手指点,先谢了。
select m.编号,m.姓名,n.年龄
from (
select b.编号,b.姓名 from b
union
select a.编号,a.姓名 from a
where not exists (select '1' from b where a.编号=b.编号)
) m,
(
select b.编号,b.年龄 from b
where b.年龄 is not null
union
select a.编号,a.年龄 from a
where not exists (select '1' from b where a.编号=b.编号)
union
select b.编号,a.年龄 from a,b
where a.编号=b.编号
and b.年龄 is null and a.年龄 is not null
) n
where m.编号=n.编号
create view v_result as select b.code,b.name,b.age from a,b
where a.code = b.code and a.name <> b.name and b.age is not null
union
select b.code,b.name,a.age from a,b
where a.code = b.code and a.name <> b.name and b.age is null
union
select b.code,b.name,b.age from a,b
where a.code = b.code and a.name = b.name and b.age is not null
union
select b.code,b.name,a.age from a,b
where a.code = b.code and a.name = b.name and b.age is null
union
select code,name,age from a where code not in(select code from b)
union
select code,name,age from b where code not in(select code from a)
UNION
(
B
MINUS
A
)
full join B on A.no=B.no