SQL查询存在重复OrderId时如何按客户维度重新编号订单序号

实现方案 #

方案1：使用DENSE_RANK()窗口函数（推荐，绝大多数现代数据库支持） #

你之前用ROW_NUMBER()不符合需求是因为它会给每一行生成唯一序号，哪怕同一订单的多条商品记录排序值相同，也会拿到不同编号。换成DENSE_RANK()就能完美解决：该函数会给同一个分组内排序值相同的行返回相同的排名，且不会出现跳号，刚好匹配「同一订单的所有商品行共享同一个客户内订单序号」的需求。
示例查询代码：

SELECT 
    cus.CustomerId, 
    DENSE_RANK() OVER (PARTITION BY cus.CustomerId ORDER BY ord.OrderId) AS CustomerOrderSeq,
    orp.ProductId
FROM 
    Customers cus
INNER JOIN 
    Orders ord ON cus.CustomerId = ord.CustomerId
INNER JOIN 
    OrderedProducts orp ON ord.OrderId = orp.OrderId
WHERE 
    cus.CustomerId = 123
ORDER BY 
    ord.OrderId, orp.ProductId

执行后原OrderId为147的所有行会返回序号1，493返回序号2，612返回序号3，完全符合预期。 #

方案2：订单预编号（兼容不支持窗口函数的旧版本数据库） #

如果使用的数据库不支持窗口函数（比如MySQL 5.7及更早版本），可以先单独给订单表生成客户维度的序号，再关联商品表即可：
示例查询代码：

SELECT 
    cus.CustomerId,
    ord_seq.CustomerOrderSeq,
    orp.ProductId
FROM 
    Customers cus
INNER JOIN (
    -- 预先给目标客户的所有订单生成序号
    SELECT 
        OrderId,
        CustomerId,
        (SELECT COUNT(DISTINCT o2.OrderId) 
         FROM Orders o2 
         WHERE o2.CustomerId = o1.CustomerId AND o2.OrderId <= o1.OrderId) AS CustomerOrderSeq
    FROM Orders o1
    WHERE o1.CustomerId = 123
) ord_seq ON cus.CustomerId = ord_seq.CustomerId
INNER JOIN OrderedProducts orp ON ord_seq.OrderId = orp.OrderId
ORDER BY 
    ord_seq.CustomerOrderSeq, orp.ProductId

该方案的逻辑是先在子查询中统计每个订单是当前客户的第几笔订单，再关联商品表，同一订单的所有商品行自然会拿到相同的序号。 #

两种方案均为查询层面实现，不需要修改原表任何数据。

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