如何基于分组后的其他列值更新某列？MySQL场景

需求说明 #

需按Delivery字段分组，每组内根据不同的Food值分配递增的唯一序号，将序号更新到Group列。具体示例如下：

表结构 #

CREATE TABLE your_table (
    Id INT,
    Delivery VARCHAR(50),
    Food VARCHAR(50),
    `Group` INT
);

初始数据 #

INSERT INTO your_table VALUES
(1, 'Ups', 'Vege', null),
(2, 'DHL', 'Vege', null),
(3, 'Ups', 'Meat', null),
(4, 'Ups', 'Vege', null),
(5, 'DHL', 'Mushroom', null),
(6, 'Fedex', 'Mushroom', null),
(7, 'Ups', 'Mushroom', null),
(8, 'Ups', 'Meat', null);

期望更新结果 #

解决方案 #

方案1：MySQL 8.0+（支持窗口函数） #

直接使用DENSE_RANK()窗口函数，按Delivery分区、Food排序生成序号，再关联原表更新：

WITH ranked_data AS (
    SELECT 
        Id,
        DENSE_RANK() OVER (PARTITION BY Delivery ORDER BY Food) AS new_group
    FROM your_table
)
UPDATE your_table t
JOIN ranked_data rd ON t.Id = rd.Id
SET t.`Group` = rd.new_group;

方案2：MySQL 5.x版本（无窗口函数） #

通过用户变量模拟分组排序逻辑，步骤如下：

1. 先为每个Delivery+Food的唯一组合分配序号
2. 关联原表完成更新

-- 生成Delivery+Food对应的分组序号临时表
CREATE TEMPORARY TABLE temp_group AS
SELECT 
    Delivery,
    Food,
    @rank := IF(@current_delivery = Delivery, @rank + IF(@current_food = Food, 0, 1), 1) AS group_num,
    @current_delivery := Delivery,
    @current_food := Food
FROM (
    SELECT DISTINCT Delivery, Food 
    FROM your_table 
    ORDER BY Delivery, Food
) AS distinct_foods,
(SELECT @current_delivery := '', @current_food := '', @rank := 0) AS init_vars;

-- 更新原表的Group列
UPDATE your_table t
JOIN temp_group tg ON t.Delivery = tg.Delivery AND t.Food = tg.Food
SET t.`Group` = tg.group_num;

-- 可选：清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_group;

说明：临时表先提取所有唯一的Delivery+Food组合并排序，通过变量判断当前行是否与上一行同属一个Delivery组，若组内Food变化则序号+1，组切换则重置序号为1，最后通过关联完成原表更新。

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