SQLite使用CTE删除electricity_meters重复行报错：找不到row_num列

问题原因分析 #

• SQLite版本限制：SQLite 3.35.0以下版本不支持直接对CTE执行DELETE操作，且即使高版本支持，直接删除CTE的行也不会影响原表（CTE是临时结果集）。
• 拼写错误：你的代码中表名power_consumtions拼写错误，应为power_consumptions；字段adressId疑似拼写错误，应为addressId，这会导致关联逻辑异常。
• 语法逻辑错误：直接从CTE删除无法关联到原表的实际数据，必须通过唯一标识（如rowid或主键）定位原表中要删除的行。

修正后的解决方案 #

方案一（适用于SQLite 3.35.0及以上版本） #

利用CTE标记需删除的行，通过rowid关联原表执行删除：

WITH CTE_table AS (
    SELECT 
        em.rowid,
        ROW_NUMBER() OVER (
            PARTITION BY em.serial_number 
            ORDER BY pc.consumed_value DESC
        ) AS row_num
    FROM electricity_meters em
    LEFT JOIN power_consumptions pc ON em.addressId = pc.addressId
)
DELETE FROM electricity_meters
WHERE rowid IN (SELECT rowid FROM CTE_table WHERE row_num > 1);

方案二（兼容低版本SQLite） #

通过子查询筛选出需要保留的行，删除不在保留列表中的数据：

DELETE FROM electricity_meters
WHERE rowid NOT IN (
    SELECT em.rowid
    FROM electricity_meters em
    LEFT JOIN power_consumptions pc ON em.addressId = pc.addressId
    GROUP BY em.serial_number
    HAVING pc.consumed_value = MAX(pc.consumed_value)
);

额外注意事项 #

• 如果electricity_meters表有主键字段，可将rowid替换为主键，避免依赖默认的rowid。
• 若同一serial_number对应多个相同最大值的consumed_value，上述方案会保留所有符合条件的行；若需仅保留一行，可在排序时加入em.rowid（如ORDER BY pc.consumed_value DESC, em.rowid）。
• 执行删除操作前务必备份数据，防止误删。

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