MySQL如何为查询结果中每个订单下的配送地点生成递增序列号

方案1：MySQL 8.0 + 版本（推荐） #

直接用窗口函数ROW_NUMBER()实现分组排序，逻辑清晰性能更好：

SELECT 
  order_id,
  ship_location_id,
  ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY ship_location_id ASC) AS sequence
FROM (
  SELECT DISTINCT oh.order_id, oline.ship_location_id
  FROM op_order_header AS oh
  INNER JOIN op_order_line AS oline ON oh.order_id = oline.order_id
  INNER JOIN co_products AS pr ON oline.product_id = pr.product_id AND pr.inventory_type = 'inventory'
  WHERE oh.order_status = 'COMPLETE'
) t

说明：PARTITION BY order_id 表示按订单ID分组，同个订单内生成序号，ORDER BY 后面的字段可以根据你实际的排序需求调整，比如要按配送地点创建时间排序就替换成对应的时间字段即可。

方案2：MySQL 5.x 及更低版本（无窗口函数支持） #

可以用用户变量模拟分组排序效果：

SELECT 
  order_id,
  ship_location_id,
  sequence
FROM (
  SELECT 
    t.order_id,
    t.ship_location_id,
    @seq := IF(@pre_order = t.order_id, @seq + 1, 1) AS sequence,
    @pre_order := t.order_id
  FROM (
    SELECT DISTINCT oh.order_id, oline.ship_location_id
    FROM op_order_header AS oh
    INNER JOIN op_order_line AS oline ON oh.order_id = oline.order_id
    INNER JOIN co_products AS pr ON oline.product_id = pr.product_id AND pr.inventory_type = 'inventory'
    WHERE oh.order_status = 'COMPLETE'
    ORDER BY oh.order_id ASC, oline.ship_location_id ASC
  ) t,
  (SELECT @seq := 0, @pre_order := NULL) init_vars
) res

说明：先初始化两个用户变量，@pre_order存储上一行的订单ID，@seq存储当前序号，遍历数据时如果当前订单ID和上一个一致就序号+1，否则重置为1，实现同订单内递增的效果。

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