如何在Oracle SQL中按年份分组生成独立连续序号列

要实现每个年份内序号从1开始递增的需求，你只需要在窗口函数中添加PARTITION BY YEAR子句，把数据按年份分组后再生成序号就行。下面是具体的解决方案：

方案1：使用ROW_NUMBER()窗口函数（推荐） #

ROW_NUMBER()是专门用来生成组内连续序号的函数，搭配PARTITION BY指定分组字段，ORDER BY指定组内排序规则，逻辑清晰且性能优异：

SELECT 
    year, 
    quarter, 
    uploaddate, 
    wagecount,
    ROW_NUMBER() OVER (PARTITION BY year ORDER BY quarter, uploaddate) AS SequentialNumbers
FROM SAMPLE_DATA
ORDER BY year, quarter, uploaddate;

方案2：修改原有的SUM()窗口函数 #

如果你想沿用原来的SUM(1)累计计数方式，只需要在OVER子句中加入PARTITION BY year，让累计计数在每个年份内重新开始：

SELECT 
    year, 
    quarter, 
    uploaddate, 
    wagecount,
    SUM(1) OVER (PARTITION BY year ORDER BY quarter, uploaddate) AS SequentialNumbers
FROM SAMPLE_DATA
ORDER BY year, quarter, uploaddate;

方案3：基于ROWNUM的兼容实现（适用于旧版Oracle） #

如果你的Oracle版本不支持窗口函数，可以通过嵌套子查询先按年份分组排序，再生成组内序号：

SELECT 
    t.year, 
    t.quarter, 
    t.uploaddate, 
    t.wagecount,
    ROW_NUMBER() OVER (PARTITION BY t.year ORDER BY t.rn) AS SequentialNumbers
FROM (
    SELECT 
        year, 
        quarter, 
        uploaddate, 
        wagecount,
        ROWNUM rn
    FROM SAMPLE_DATA
    ORDER BY year, quarter, uploaddate
) t;

验证结果 #

执行上述任意语句后，都会得到你期望的按年份分组的序号效果：

样本数据参考 #

如果你需要测试，可以使用以下建表和插入语句：

CREATE TABLE SAMPLE_DATA ( 
    YEAR VARCHAR2(4) NULL, 
    QUARTER NUMBER(1,0) NULL, 
    UPLOADDATE DATE NULL, 
    WAGECOUNT NUMBER(10,0) NULL 
); 

Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('27-MAR-19','DD-MON-RR'),5); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('28-MAR-19','DD-MON-RR'),8493); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('29-MAR-19','DD-MON-RR'),15070); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('30-MAR-19','DD-MON-RR'),1244); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('03-JAN-20','DD-MON-RR'),0); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('05-JAN-20','DD-MON-RR'),2); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('06-JAN-20','DD-MON-RR'),3); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('07-JAN-20','DD-MON-RR'),6); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('21-APR-21','DD-MON-RR'),59); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('22-APR-21','DD-MON-RR'),10); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('23-APR-21','DD-MON-RR'),16); 
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('24-APR-21','DD-MON-RR'),1);

内容的提问来源于stack exchange，提问作者cmomah