MySQL如何按航空公司订单统计数量更新air_line_info表sort排序字段

原SQL存在的问题 #

• WHERE条件中使用=匹配多行子查询结果，子查询SELECT air_line_code FROM orders GROUP BY air_line_code会返回3条航司编码数据，=运算符仅支持匹配单个值，会直接抛出语法/逻辑错误。
• 子查询的排序逻辑错误，你需要订单量越高sort值越小，所以统计后应该按COUNT(order_id) DESC降序排序，原语句是默认升序，就算执行成功排序结果也是反的。
• UPDATE语句没有和统计订单量的结果集关联，行号赋值的顺序并没有和航司订单量的排序绑定，就算WHERE条件改对，sort赋值的顺序也是随机的，不符合需求。

可行实现方案 #

方案1：MySQL 8.0及以上版本（使用窗口函数，逻辑更清晰） #

UPDATE air_line_info a
INNER JOIN (
    SELECT 
        air_line_code,
        -- 按订单量降序排序生成排名
        ROW_NUMBER() OVER(ORDER BY COUNT(order_id) DESC) AS new_sort
    FROM orders
    GROUP BY air_line_code
) b ON a.code = b.air_line_code
SET a.sort = b.new_sort;

方案2：MySQL 5.x版本（使用用户变量实现排名） #

SET @sort = 0;
UPDATE air_line_info a
INNER JOIN (
    SELECT 
        air_line_code,
        (@sort := @sort + 1) AS new_sort
    FROM (
        -- 先统计订单量并按降序排序
        SELECT air_line_code 
        FROM orders 
        GROUP BY air_line_code 
        ORDER BY COUNT(order_id) DESC
    ) t
) b ON a.code = b.air_line_code
SET a.sort = b.new_sort;

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