ORACLE创建一个视图表A中含有表B的,将表B的字段替换表A的字段
程序员文章站
2022-03-28 13:53:28
...
说明:表A中包含表B,表B是表A的子集 经常有一个需求,表B中的数据是处理后的,表A中的数据是原始数据,那么如何获取更新后的数据呢?
处理办法就是使用left join ,这样应该是最优效率的,如果有更好的办法,请大家列出来一起学习研究
- 模拟表A
select
A.id,A.name
from (
select 1 as id ,'张三' as name from dual
union all
select 2 as id ,'李四' as name from dual
) A
- 模拟表B
select
B.id,B.name
from (
select 1 as id ,'王五' as name from dual
) A
- 求更新后的数据
select A.id,case when B.id is not null then B.NAME else A.Name end as name
from (
select 1 as id ,'张三' as name from dual
union all
select 2 as id ,'李四' as name from dual
) A
left join
(
select 1 as id ,'王五' as name from dual
) B on A.Id = b.id