随笔小计

create table aa1
(name varchar(12),
id int,
age int);

alter table ovp.aa1
add column timew_1 data type varchar(20);

create table aa2
(name varchar(12),
id int,
age int,
time varchar(20))

create table aa3
(name varchar(12),
id int,
age int,
time varchar(20))

insert into aa2
values ('wfq',1987,24,'201101')
insert into aa2
values ('wfq',1987,24,'201101')
insert into aa2
values ('wfq',1987,24,'201102')
insert into aa2
values ('wfq',1987,25,'201102')

insert into aa1
values ('wfq',1987,24);
insert into aa1
values ('wfq',1987,25);

select row_number()over(partition by name,id order by time desc) as row_num,
name,id,age,time from aa2;


MERGE INTO OVP.aa1 AS tgt
USING (

SELECT DISTINCT name, id
FROM OVP.aa2
)AS src
ON (tgt.name= src.name AND tgt.id = src.id)
WHEN MATCHED
THEN
DELETE

ELSE IGNORE;


insert into ovp.aa3

select distinct name,id,age,time from ovp.aa2

select * from aa1;
select * from aa2;
select * from aa3

alter table ovp.aa1 activate not logged initially with empty table