数据库操作之update，按照特定字段进行分组排序update更新序号

需求：根据project_id分组，project_lease_item_id排序对seq_number从1递增更新整个表中的数据

一、下图为更新之前的数据：

 二、更新数据库的代码

数据库用的是oracle:

方案一：

update prj_project_lease_item p1
   set p1.seq_number =
       (select V.seq_number
          FROM (select row_number() over(partition by p.project_id order by project_lease_item_id asc) seq_number,
                       p.project_lease_item_id
                  from prj_project_lease_item p) V
         where V.project_lease_item_id = p1.project_lease_item_id)
 where exists
 (select p.project_id,
               p.project_lease_item_id,
               row_number() over(partition by p.project_id order by project_lease_item_id asc) seq_number
          from prj_project_lease_item p
         where p.project_lease_item_id = p1.project_lease_item_id);

方案二：

declare

  v_temp prj_project_lease_item%rowtype;

begin

  for v_temp in (select p.project_id,
                        p.project_lease_item_id,
                        row_number() over(partition by p.project_id order by project_lease_item_id asc) seq_number
                   from prj_project_lease_item p) loop
  
    update prj_project_lease_item p2
       set p2.seq_number = v_temp.seq_number
     where p2.project_lease_item_id = v_temp.project_lease_item_id;
  
  end loop;

end;

三、更新之后的数据如下图所示