Oracle 19c中带字母前缀的编号连续范围展示需求

解决方案思路 #

• 拆分编码为字母前缀与数字部分，将数字转为数值类型以便判断连续性
• 按前缀分组，通过窗口函数生成连续数字组的标识（连续数字的ROW_NUMBER() - 数字值结果相同）
• 按前缀和分组标识聚合，获取每组的首尾编码
• 根据首尾编码是否相同，拼接成单个编码或范围格式，最后合并所有结果

完整SQL代码 #

WITH split_codes AS (
    SELECT 
        code,
        -- 提取开头所有非数字字符作为前缀
        REGEXP_SUBSTR(code, '^[^0-9]+') AS prefix,
        -- 提取末尾数字并转为数值型
        TO_NUMBER(REGEXP_SUBSTR(code, '[0-9]+$')) AS num_val
    FROM your_table
),
grouped_codes AS (
    SELECT 
        prefix,
        num_val,
        code,
        -- 连续数字组的标识：连续序列中该行号与数字值的差固定
        ROW_NUMBER() OVER (PARTITION BY prefix ORDER BY num_val) - num_val AS group_id
    FROM split_codes
),
range_codes AS (
    SELECT 
        prefix,
        MIN(code) AS start_code,
        MAX(code) AS end_code
    FROM grouped_codes
    GROUP BY prefix, group_id
    ORDER BY prefix, start_code
)
SELECT LISTAGG(
    CASE 
        WHEN start_code = end_code THEN start_code
        ELSE start_code || '-' || end_code
    END, '、'
) WITHIN GROUP (ORDER BY prefix, start_code) AS result
FROM range_codes;

代码说明 #

1. split_codes CTE：拆分原始编码，用正则表达式分离字母前缀和数字部分，将数字转为数值类型是判断连续性的关键。
2. grouped_codes CTE：按前缀分组后，对数字值排序生成行号，行号与数字值的差值可标记连续数字组——连续的数字序列中，这个差值会保持一致。
3. range_codes CTE：按前缀和分组标识聚合，得到每个连续组的首尾编码。
4. 最终查询：用LISTAGG将结果拼接成指定格式，同一组首尾编码相同则输出单个编码，否则输出范围。

测试验证 #

针对示例输入的编码集合，执行上述SQL后会输出：

A1-A5、A8-A12、B3、B5、B7-B9、B110-B112、C1-C8

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