用两层SQL 第一层分组求合,把相同的product_id charge_id 的合并数据 第二层用row_number() over(partition by product_id order by charge_id ) 来生成 SEQ
select a.*,row_number() over(partition by product_id order by charge_id) seq from ( select product_id,charge_id sum(a_amount) a_amount, sum(b_amount) b_amount, sum(a_disc_amount)a_disc_amount, sum(b_disc_amount)b_disc_amount from table group by product_id,charge_id ) a
create or replace procedure p1 is begin create table s_table_bak as select * from s_table;--table 是你的源表
delete from s_table; alter table s_table add column seq number;insert into s_table select a.*,row_number() over(partition by product_id order by charge_id) seq from ( select product_id,charge_id sum(a_amount) a_amount, sum(b_amount) b_amount, sum(a_disc_amount)a_disc_amount, sum(b_disc_amount)b_disc_amount from s_table_bak group by product_id,charge_id ) a end p1;
这个也不难的
用ROW_NUMBER 这个分析函数就行了
第一层分组求合,把相同的product_id charge_id 的合并数据
第二层用row_number() over(partition by product_id order by charge_id ) 来生成 SEQ
from
(
select product_id,charge_id
sum(a_amount) a_amount,
sum(b_amount) b_amount,
sum(a_disc_amount)a_disc_amount,
sum(b_disc_amount)b_disc_amount
from table
group by product_id,charge_id
) a
一定要过程的话,基本上将该sql带入,稍作处理就行了
create or replace procedure p1
is
begin
create table s_table_bak as select * from s_table;--table 是你的源表
delete from s_table; alter table s_table add column seq number;insert into s_table
select a.*,row_number() over(partition by product_id order by charge_id) seq
from
(
select product_id,charge_id
sum(a_amount) a_amount,
sum(b_amount) b_amount,
sum(a_disc_amount)a_disc_amount,
sum(b_disc_amount)b_disc_amount
from s_table_bak
group by product_id,charge_id
) a
end p1;