如何为SQLite表中的重复scene_id条目添加字母后缀？

需求说明 #

现有scenes表数据如下：

id  created_at  updated_at  scene_id
--  ----------  ----------  ---------
1   2024-02-13  2024-03-05  VT-1105
2   2024-02-14  2024-03-06  KA-338
3   2024-02-15  2024-03-07  JU-182
4   2024-02-16  2024-03-08  PX-118
5   2024-02-17  2024-03-09  PO-6339085
6   2024-02-18  2024-03-10  LA-426
7   2024-03-03  2024-03-06  KA-338
8   2024-03-03  2024-03-06  KA-338
9   2024-03-03  2024-03-06  KA-338
10  2024-03-04  2024-03-07  JU-182
11  2024-03-04  2024-03-07  JU-182
12  2024-03-04  2024-03-07  JU-182
13  2024-03-04  2024-03-07  JU-182
14  2024-03-05  2024-03-07  PX-118
15  2024-03-05  2024-03-07  PX-118

需要实现：每组scene_id的第一条记录保留原名称，后续重复条目依次添加-A、-B、-C等后缀，最终效果如下：

id  created_at  updated_at  scene_id
--  ----------  ----------  ---------
1   2024-02-13  2024-03-05  VT-1105
2   2024-02-14  2024-03-06  KA-338
3   2024-02-15  2024-03-07  JU-182
4   2024-02-16  2024-03-08  PX-118
5   2024-02-17  2024-03-09  PO-6339085
6   2024-02-18  2024-03-10  LA-426
7   2024-03-03  2024-03-06  KA-338-A
8   2024-03-03  2024-03-06  KA-338-B
9   2024-03-03  2024-03-06  KA-338-C
10  2024-03-04  2024-03-07  JU-182-A
11  2024-03-04  2024-03-07  JU-182-B
12  2024-03-04  2024-03-07  JU-182-C
13  2024-03-04  2024-03-07  JU-182-D
14  2024-03-05  2024-03-07  PX-118-A
15  2024-03-05  2024-03-07  PX-118-B

解决方案 #

使用CASE WHEN结合窗口函数ROW_NUMBER()来区分每组的第一条记录和后续重复记录，避免调用CHAR(NULL)导致的报错：

SELECT 
    id,
    created_at,
    updated_at,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY scene_id ORDER BY id) = 1 THEN scene_id
        ELSE scene_id || '-' || CHAR(64 + (ROW_NUMBER() OVER (PARTITION BY scene_id ORDER BY id) - 1))
    END AS scene_id
FROM scenes
ORDER BY id ASC;

原理说明 #

1. 窗口函数分组排序：ROW_NUMBER() OVER (PARTITION BY scene_id ORDER BY id)会按scene_id分组，每组内按id升序生成行号，确保每组最早的记录行号为1。
2. 条件判断拼接：
   • 当行号为1时，直接返回原scene_id，保留原始名称；
   • 当行号大于1时，计算后缀字母：ROW_NUMBER()-1得到从1开始的序号，加上64（ASCII码中'A'为65），通过CHAR()函数转换为对应的大写字母，再与原scene_id拼接。
3. 避免NULL问题：该写法无需处理NULL值，不会触发CHAR(NULL)的报错，同时精准控制仅对重复条目添加后缀。

扩展说明 #

如果单组重复条目超过26个（需要-Z之后的后缀），可以考虑使用数字+字母的组合方式，或者自定义映射逻辑，但当前方案完全满足需求中的场景。

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