select * from test_all; --全量的数据
insert into test_all (ID, NAME, FISRT_FLG)
values ('1', 'aaa', '1');
insert into test_all (ID, NAME, FISRT_FLG)
values ('2', 'bbc', '1');
insert into test_all (ID, NAME, FISRT_FLG)
values ('3', 'ccc', '1');
insert into test_all (ID, NAME, FISRT_FLG)
values ('4', 'ddd', '1');
commit;
select* from test_his; --历史全量数据(有切片)
insert into test_his (ID, NAME, DATA_DT, FIRST_FLG)
values ('1', 'aaa', '20221014', '0');
insert into test_his (ID, NAME, DATA_DT, FIRST_FLG)
values ('2', 'bbb', '20221015', '0');
commit;
--更新fisrt_flg值
update test_all t1
set t1.fisrt_flg = '0'
where exists (select 1 from test_his t2 /***应该取最新一条数据防止反复变化****/
where t1.id = t2.id);
commit;
select * from test1; --要回传的数据`
insert into test1
select * from test_all t1
where t1.fisrt_flg='0'
and not exists (
select 1 from test_his t2 where /***最新一条数据***/
t1.id||t1.name = t1.id||t2.name
--and t1.name = t2.name
and t2.first_flg = '0'
);
commit;
向历史表插入数据;
insert into test_his
select
t1.id,
t1.name,
to_char(sysdate,'yyyymmdd'),
'1' as fisrt_flg
from test_all t1
where not exists (
select 1 from test_his t2
where t1.id||t1.name = t2.id||t2.name
);
commit;
程序之前记得删除his表当天的历史数据;支持重跑
标签:his,into,t2,笔记,t1,学习,test,oracle,select From: https://www.cnblogs.com/diablo-427/p/16797670.html