产仔数据订正.sql

delete from tbl_produce
where id not in (
select max(p.id) from  tbl_produce p join tbl_animal_info a on p.animal_id= a.id 
group by a.rfid , p.produce_date,animal_id 
);



declare
v_date date;
v_id number(20);
cursor r is 
select animal_id,count(*) as num from tbl_produce group by  animal_id ;


thedata r%rowtype;

begin 
  
  update tbl_produce
  set production_num=null;
  commit;
  open r ;
  loop
  fetch  r into thedata;
  exit when r%notfound;
  
     
          for i in 1..thedata.num
          loop
          
           select min(produce_date) into  v_date  from tbl_produce where  animal_id=thedata.animal_id 
           and production_num is  null order by produce_date  ;
         
            update  tbl_produce
            set  production_num=i
             where animal_id=thedata.animal_id and produce_date=v_date;
             commit;  
  end loop;
   end loop;     
  end;