利用分析函数ROW_NUMBER实现分组排序
按照特定的列(a0100)进行分组,按照创建日期(date)更改排序字段的值
select z.a0100,z.i9999,z.createtime,sum(z.cz604)
from
usraz6 z
group by z.a0100,z.i9999,z.createtime
order by z.a0100,z.i9999
使用Oracle 分析函数,现在组内进行排序,然后再循环更新字段
declare
v_sql_id usraz6%rowtype;
begin
for v_sql_id in (select z.a0100,
z.createtime,
row_number() over(partition by z.a0100 order by z.createtime asc) seq_number
from usraz6 z ) loop
update usraz6 p2
set p2.i9999 = v_sql_id.seq_number
where p2.createtime = v_sql_id.createtime;
end loop;
end;