SQLite：删除指定颜色行后按国家重新生成color_order序号

我的表中每个颜色都有color_order字段，用来定义同一国家内的颜色排序规则，不同国家的排序逻辑可能不一样。

原始数据 #

需求 #

删除所有red颜色的行后，给每个国家重新生成color_order编号，要求同一国家里同颜色的行必须共用同一个color_order，而且编号要按照原color_order的顺序连续排列。预期结果如下：

预期结果 #

我初步想使用ROW_NUMBER() OVER (PARTITION BY country ORDER BY color_order)，求具体的实现方案。

你选窗口函数的思路是对的，但ROW_NUMBER()会给每行生成唯一序号，不符合同颜色行共用同一个color_order的要求。换成DENSE_RANK()就可以解决这个问题——它能在分组内给相同排序条件的记录分配相同的排名，而且排名是连续的。

基础查询方案（直接获取处理后的结果） #

SELECT
    country,
    color,
    DENSE_RANK() OVER (
        PARTITION BY country
        ORDER BY original_color_order
    ) AS color_order,
    other_data
FROM (
    -- 先过滤掉red行，保留原始排序字段
    SELECT
        country,
        color,
        color_order AS original_color_order,
        other_data
    FROM your_table_name
    WHERE color != 'red'
) filtered_rows;

逻辑说明 #

1. 内层子查询filtered_rows先把所有color = 'red'的行过滤掉，同时把原始的color_order重命名为original_color_order，用来作为后续重新排序的依据。
2. 外层用DENSE_RANK()，按country分组，再按原始的color_order排序，这样同一国家里，原排序靠前的颜色会被分配更小的新编号，同颜色的所有行也会得到相同的color_order。

如果需要更新原表的color_order #

如果不是只需要查询结果，而是要修改原表的数据，以MySQL为例可以这么做（不同数据库语法略有区别）：

-- 创建临时表存储处理后的新数据
CREATE TEMPORARY TABLE temp_updated_data AS
SELECT
    country,
    color,
    DENSE_RANK() OVER (
        PARTITION BY country
        ORDER BY color_order
    ) AS new_color_order,
    other_data,
    your_primary_key -- 替换成你的表主键，用来准确匹配行
FROM your_table_name
WHERE color != 'red';

-- 删除原表中的red行
DELETE FROM your_table_name WHERE color = 'red';

-- 更新剩余行的color_order
UPDATE your_table_name t
JOIN temp_updated_data tu
    ON t.your_primary_key = tu.your_primary_key
SET t.color_order = tu.new_color_order;

-- 删除临时表
DROP TEMPORARY TABLE temp_updated_data;

注意：一定要用主键关联更新，避免因为other_data为空或重复导致错误匹配。

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