如何用单条SQL语句按类别更新商品价格排名字段？

要实现按cat_id分组、每组内按price降序分配排名（每组从1开始计数），你需要新增变量记录上一个类别的ID，在类别切换时重置排名计数器。修改后的完整SQL语句如下：

UPDATE `test1` t1
JOIN (
    SELECT 
        `id`,
        @rank := CASE 
            WHEN @prev_cat = `cat_id` THEN @rank + 1 
            ELSE 1 
        END AS `new_rank`,
        @prev_cat := `cat_id`
    FROM `test1`, (SELECT @rank := 0, @prev_cat := NULL) init_vars
    ORDER BY `cat_id` ASC, `price` DESC
) t2 ON t1.`id` = t2.`id`
SET t1.`rank` = t2.`new_rank`;

核心逻辑说明 #

• 变量初始化：(SELECT @rank := 0, @prev_cat := NULL) init_vars 初始化两个关键变量：@rank用于统计当前类别的排名，@prev_cat存储上一行的类别ID，用于判断是否需要重置排名。
• 排名计算规则：
  • 若当前行cat_id与@prev_cat一致，说明属于同一类别，@rank自增1；
  • 若类别发生变化，则将@rank重置为1，开启新类别的排名计数；
  • 每次计算后更新@prev_cat为当前行的cat_id，供下一行判断使用。
• JOIN更新方式：通过子查询先计算出所有商品的新排名，再通过id关联原表完成更新——这是因为MySQL不允许在UPDATE语句中直接引用正在更新的表的变量查询结果，会导致数据不一致。

效果验证示例 #

假设表中有初始数据：

执行上述SQL后，rank字段会被更新为：

完美实现了每个类别内按价格从高到低的独立排名要求。

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