有一張產(chǎn)品生產(chǎn)日期表受葛，字段有批次號(hào)偎谁，產(chǎn)品名，生產(chǎn)日期巡雨。結(jié)構(gòu)以及測(cè)試數(shù)據(jù)如下：

CREATE TABLE "VICEL"."TEST" 
   (    "BATCH" VARCHAR2(255), 
    "NAME" VARCHAR2(255), 
    "PRODUCE_DATE" DATE
   ) ;

INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '產(chǎn)品1', TO_DATE('2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '產(chǎn)品1', TO_DATE('2021-05-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '產(chǎn)品3', TO_DATE('2021-05-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '產(chǎn)品3', TO_DATE('2021-05-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '產(chǎn)品2', TO_DATE('2021-05-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '產(chǎn)品2', TO_DATE('2021-05-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '產(chǎn)品2', TO_DATE('2021-05-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '產(chǎn)品1', TO_DATE('2021-05-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '產(chǎn)品1', TO_DATE('2021-05-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '產(chǎn)品3', TO_DATE('2021-05-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '產(chǎn)品3', TO_DATE('2021-05-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '產(chǎn)品3', TO_DATE('2021-05-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '產(chǎn)品2', TO_DATE('2021-05-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '產(chǎn)品2', TO_DATE('2021-05-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '產(chǎn)品1', TO_DATE('2021-05-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));


select 
-- 序號(hào)規(guī)則：按批次分組铐望，再按生產(chǎn)日期排序
row_number() over(partition by batch order by produce_date )  rn,
batch,
name,
produce_date
from test;
select 
-- 序號(hào)規(guī)則：按批次分組茂附，再按名稱分組督弓，最后按生產(chǎn)日期排序
row_number() over(partition by batch,name order by produce_date )  rn,
batch,
name,
produce_date
from test