如何在Oracle查询中基于报表字段生成临时Origin ID序列？

要实现你需要的Temporary Origin ID生成规则，我们可以结合字符串截取函数和窗口排名函数来完成，具体步骤和SQL语句如下：

核心思路拆解 #

• 第一步：提取Origin ID中横杠前的前缀部分，用SUBSTR和INSTR函数定位横杠的位置来截取。
• 第二步：给不同的Origin Name分配连续递增的序号，这里用DENSE_RANK()窗口函数，它会为相同的Origin Name返回同一个序号，且序号不会跳号（正好符合你的001、002...需求）。
• 第三步：把序号格式化为3位的固定长度，不足补0，用LPAD函数实现。
• 第四步：把前缀和格式化后的序号拼接成最终的Temporary Origin ID。

完整查询语句 #

SELECT 
    origin_id,
    origin_name,
    CONCAT(
        SUBSTR(origin_id, 1, INSTR(origin_id, '-') - 1),
        LPAD(DENSE_RANK() OVER (ORDER BY origin_name), 3, '0')
    ) AS temporary_origin_id
FROM origin_tab
ORDER BY origin_id;

语句细节解释 #

1. SUBSTR(origin_id, 1, INSTR(origin_id, '-') - 1)：
   • INSTR(origin_id, '-')找到origin_id中第一个横杠的位置，减1就是前缀的最后一位索引，SUBSTR从第一位开始截取到这个位置，得到类似1111这样的前缀。
2. DENSE_RANK() OVER (ORDER BY origin_name)：
   • 按照origin_name排序，为每个不同的origin_name分配一个连续的排名，比如AAA对应1，BBB对应2，以此类推，相同名称的记录排名相同。
3. LPAD(..., 3, '0')：
   • 把排名数字补成3位长度，比如1变成001，2变成002，保证格式统一。
4. CONCAT(...)：
   • 把前缀和格式化后的排名拼接起来，得到最终的Temporary Origin ID。

验证示例数据 #

用你给出的示例数据测试，这个查询会返回和你预期完全一致的结果：

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