SQL循环迭代需求：单次Loop能否按cd_number分批取数？有无替代方案？

我需要从表中按批次提取数据，每个批次里每个cd_number各取一条记录（按记录的时间顺序分配批次），之前尝试用循环但没做好，想问有没有不用循环的SQL方法实现这个需求？

数据表结构与示例数据 #

-- 创建表
CREATE TABLE tbl28 (cd_Number int, date_begin date, date_end date );

-- 插入示例数据（统一转换为日期类型避免格式问题）
INSERT INTO tbl28 (cd_Number,date_begin,date_end)
SELECT 1,  TO_DATE('03-APR-17', 'DD-MON-RR'), TO_DATE('03-JUN-19', 'DD-MON-RR') FROM DUAL UNION ALL
SELECT 1,  TO_DATE('25-FEB-19', 'DD-MON-RR'), TO_DATE('03-JUN-19', 'DD-MON-RR') FROM DUAL UNION ALL
SELECT 1,  TO_DATE('13-MAR-20', 'DD-MON-RR'), TO_DATE('30-JUN-23', 'DD-MON-RR') FROM DUAL UNION ALL
SELECT 2,  TO_DATE('02-NOV-17', 'DD-MON-RR'), TO_DATE('30-APR-18', 'DD-MON-RR') FROM DUAL UNION ALL
SELECT 2,  TO_DATE('25-FEB-19', 'DD-MON-RR'), TO_DATE('11-OCT-19', 'DD-MON-RR') FROM DUAL UNION ALL
SELECT 3,  TO_DATE('31-DEC-16', 'DD-MON-RR'), TO_DATE('30-OCT-17', 'DD-MON-RR') FROM DUAL UNION ALL
SELECT 3,  TO_DATE('21-OCT-21', 'DD-MON-RR'), TO_DATE('16-FEB-22', 'DD-MON-RR') FROM DUAL UNION ALL
SELECT 3,  TO_DATE('19-OCT-22', 'DD-MON-RR'), TO_DATE('30-JUN-23', 'DD-MON-RR') FROM DUAL;

预期输出结果 #

• 第一批次

  cd_number	date_begin	date_end
  1	03-APR-17	03-JUN-19
  2	02-NOV-17	30-APR-18
  3	31-DEC-16	30-OCT-17

• 第二批次

  cd_number	date_begin	date_end
  1	25-FEB-19	03-JUN-19
  2	25-FEB-19	11-OCT-19
  3	21-OCT-21	16-FEB-22

• 第三批次

  cd_number	date_begin	date_end
  1	13-MAR-20	30-JUN-23
  3	19-OCT-22	30-JUN-23

解决方案：使用窗口函数ROW_NUMBER() #

可以通过ROW_NUMBER()窗口函数给每个cd_number组内的记录按date_begin排序编号，这个编号就是批次号，无需循环即可实现需求：

SELECT
    批次号,
    cd_number,
    TO_CHAR(date_begin, 'DD-MON-RR') AS date_begin,
    TO_CHAR(date_end, 'DD-MON-RR') AS date_end
FROM (
    SELECT
        cd_number,
        date_begin,
        date_end,
        -- 按cd_number分组，组内按date_begin升序分配批次号
        ROW_NUMBER() OVER (PARTITION BY cd_number ORDER BY date_begin) AS 批次号
    FROM tbl28
) t
ORDER BY 批次号, cd_number;

逻辑说明 #

• PARTITION BY cd_number：将数据按cd_number分成独立的组
• ORDER BY date_begin：组内按起始日期排序，确保记录按时间顺序分配到批次
• 外层查询按批次号排序，自动将同一批次的记录聚合在一起，完全匹配预期结果

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