求SQL Server查询语句：为product表生成分组递增的target_rev_id

嘿，我来帮你搞定这个按组生成递增字母序列的需求！根据你给出的输入和期望输出，核心是给每个source_item_id分组内的行生成依次递增的AAA、AAB、AAC格式的ID，下面分不同数据库给出具体的实现方案。

核心思路 #

我们需要两步走：

• 第一步：用窗口函数ROW_NUMBER()给每个source_item_id分组内的行按顺序编号（比如同一组内第一行是1，第二行是2，以此类推）。
• 第二步：把生成的数字编号转换成AAA、AAB这样的三位大写字母序列，本质是把数字转成26进制的变种（从A开始，对应0偏移）。

MySQL 实现代码 #

SELECT 
    source_item_id,
    source_rev_id,
    CONCAT(
        CHAR(FLOOR((rn - 1) / (26 * 26)) + 65),
        CHAR(FLOOR((rn - 1) / 26) % 26 + 65),
        CHAR((rn - 1) % 26 + 65)
    ) AS target_rev_id
FROM (
    SELECT 
        source_item_id,
        source_rev_id,
        ROW_NUMBER() OVER(PARTITION BY source_item_id ORDER BY source_rev_id) AS rn
    FROM product
    -- 如果你需要像示例中444那样新增一行l_04_tz，就加上下面这行
    -- UNION ALL SELECT 444, 'l_04_tz', NULL
) t
ORDER BY source_item_id, rn;

PostgreSQL 实现代码 #

SELECT 
    source_item_id,
    source_rev_id,
    CONCAT(
        CHR(FLOOR((rn - 1) / (26 * 26)) + 65),
        CHR(FLOOR((rn - 1) / 26) % 26 + 65),
        CHR((rn - 1) % 26 + 65)
    ) AS target_rev_id
FROM (
    SELECT 
        source_item_id,
        source_rev_id,
        ROW_NUMBER() OVER(PARTITION BY source_item_id ORDER BY source_rev_id) AS rn
    FROM product
    -- 如需新增行，取消注释下面这行
    -- UNION ALL SELECT 444, 'l_04_tz', NULL
) t
ORDER BY source_item_id, rn;

SQL Server 实现代码 #

SELECT 
    source_item_id,
    source_rev_id,
    CONCAT(
        CHAR(FLOOR((rn - 1) / (26 * 26)) + 65),
        CHAR(FLOOR((rn - 1) / 26) % 26 + 65),
        CHAR((rn - 1) % 26 + 65)
    ) AS target_rev_id
FROM (
    SELECT 
        source_item_id,
        source_rev_id,
        ROW_NUMBER() OVER(PARTITION BY source_item_id ORDER BY source_rev_id) AS rn
    FROM product
    -- 如需新增行，取消注释下面这行
    -- UNION ALL SELECT 444, 'l_04_tz', NULL
) t
ORDER BY source_item_id, rn;

关键说明 #

• 排序控制：ORDER BY source_rev_id是为了保证target_rev_id的生成顺序和source_rev_id的排序一致，如果不需要这个顺序，可以改成你需要的排序字段，或者去掉（但建议保留，保证结果稳定）。
• 序列扩展：这个方案支持最多17576行每组（262626），如果你的分组行数超过这个数，可以扩展成四位字母，逻辑类似，多加一位计算即可。
• 新增行处理：示例中444的l_04_tz是原始表没有的行，所以需要用UNION ALL手动添加，否则不会出现在结果里。

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