Oracle SQL如何实现问答分层按组生成带层级的枚举序号

实现思路 #

1. 先识别所有主问题：Question_Answer_id为空的行即为主问题，按日期升序排序生成三位格式的主序号
2. 识别所有回答：Question_Answer_id不为空的行即为对应主问题的子回答，按所属主问题分组，组内按日期升序排序生成三位子序号
3. 主问题直接返回主序号，回答返回「主序号.子序号」的拼接结果

实现代码 #

记得把代码中的your_table_name替换为你实际的表名：

WITH main_question AS (
    -- 生成主问题的三位序号
    SELECT 
        Question_Id,
        TO_CHAR(ROW_NUMBER() OVER(ORDER BY "Date" ASC), 'FM000') AS main_order
    FROM your_table_name
    WHERE Question_Answer_id IS NULL
)
SELECT 
    t.Question_Id,
    t.Question_text,
    t.Question_Answer_id,
    t."Date",
    CASE 
        WHEN t.Question_Answer_id IS NULL THEN m.main_order
        ELSE m.main_order || '.' || TO_CHAR(ROW_NUMBER() OVER(PARTITION BY t.Question_Answer_id ORDER BY t."Date" ASC), 'FM000')
    END AS "Order"
FROM your_table_name t
LEFT JOIN main_question m 
    ON NVL(t.Question_Answer_id, t.Question_Id) = m.Question_Id
ORDER BY t."Date" ASC;

关键说明 #

• DATE、ORDER属于Oracle保留关键字，查询时用双引号包裹避免语法报错
• TO_CHAR(数值, 'FM000')用于将数字转为3位字符串，不足位自动补前导零，FM参数用于消除生成字符串的前导空格
• 关联逻辑通过NVL(t.Question_Answer_id, t.Question_Id)实现，主问题和对应回答都会关联到对应主问题的序号
• 回答序号通过PARTITION BY Question_Answer_id分组生成，每个主问题下的回答单独排序，不会跨主问题乱序

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