SQLite按指定列分组为表添加自增groupid的实现方案

针对你的需求——按projectName、fileName、fileLine分组，为每组分配唯一自增ID并更新groupid字段，以下是适配SQLite的两种可行方案：

方案一：使用窗口函数（SQLite 3.25.0+ 推荐） #

SQLite 3.25.0及以上版本支持窗口函数，用DENSE_RANK()或ROW_NUMBER()可以快速给每个分组生成唯一ID，直接关联更新原表即可：

UPDATE my_data
SET groupid = (
    SELECT group_num
    FROM (
        SELECT 
            projectName, 
            fileName, 
            fileLine,
            -- 按分组字段排序，生成自增ID
            DENSE_RANK() OVER (ORDER BY projectName, fileName, fileLine) AS group_num
        FROM my_data
        GROUP BY projectName, fileName, fileLine
    ) AS grouped_data
    WHERE grouped_data.projectName = my_data.projectName
      AND grouped_data.fileName = my_data.fileName
      AND grouped_data.fileLine = my_data.fileLine
);

说明： #

• DENSE_RANK()会为每个唯一的分组组合生成连续的ID（不会因为分组跳过数字），如果用ROW_NUMBER()效果类似，只是当有重复排序字段时也会生成连续ID，这里两者都适用。
• 该方案无需创建临时表，语句简洁高效。

方案二：兼容低版本SQLite（无窗口函数） #

如果你的SQLite版本低于3.25.0，无法使用窗口函数，可以通过临时表+计数的方式实现：

BEGIN TRANSACTION;

-- 创建临时表存储分组与对应ID
CREATE TEMP TABLE temp_group_mapping (
    projectName TEXT,
    fileName TEXT,
    fileLine TEXT,
    group_num INTEGER
);

-- 插入去重后的分组，并为每个分组分配自增ID
INSERT INTO temp_group_mapping
SELECT 
    t.projectName,
    t.fileName,
    t.fileLine,
    -- 通过计数已插入的分组数量生成自增ID
    (SELECT COUNT(*) FROM temp_group_mapping) + 1 AS group_num
FROM (SELECT DISTINCT projectName, fileName, fileLine FROM my_data) AS t
ORDER BY projectName, fileName, fileLine;

-- 更新原表的groupid字段
UPDATE my_data
SET groupid = temp_group_mapping.group_num
FROM temp_group_mapping
WHERE my_data.projectName = temp_group_mapping.projectName
  AND my_data.fileName = temp_group_mapping.fileName
  AND my_data.fileLine = temp_group_mapping.fileLine;

-- 清理临时表
DROP TABLE temp_group_mapping;

COMMIT;

说明： #

• 用事务包裹操作，避免中途出错导致数据不一致。
• 通过SELECT COUNT(*) FROM temp_group_mapping在插入时动态生成自增ID，确保每个分组ID唯一且连续。

注意事项 #

1. 执行更新前建议备份表数据，避免操作失误导致数据丢失。
2. 如果表数据量极大，方案二的临时表方式可能需要更多内存，建议优先升级SQLite版本使用方案一。

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