随笔小计

 

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