如何将多行订单数据合并为单行结果？

源数据示例 #

OrderStatus | CustomerID | OrderNbr | LoadNbr | Product | Quantity
------------+------------+----------+---------+---------+---------
   OPEN     |     1      | ORD00001 |    1    |  0012   | 12
   OPEN     |     1      | ORD00001 |    2    |  0024   | 20

期望结果 #

OrderStatus | CustomerID | OrderNbr | Prod01 | Quantity01 | Prod02 | Quantity02 | Ratio01 | Ratio02
-------------+------------+----------+--------+------------+--------+------------+---------+---------
    OPEN     |     1      | ORD00001 |  0012  |     12     |  0024  |   20       |  37.5   |   62.5

注：示例仅含2种产品，实际数据最多可达8种。

现有尝试方案 #

WITH OrderSummary AS (
  SELECT O.OrderStatus,
         O.CustomerID,
         O.OrderNbr,
         'TotalQty' = I1.Quantity + I2.Quantity,
         'Prod01' = I1.Product,
         'Quantity01' = I1.Quantity,
         'Prod02' = I2.Product,
         'Quantity02' = I2.Quantity
  FROM   Orders O
  LEFT JOIN Orders I1 ON O.OrderNbr = I1.OrderNbr AND I1.LoadNbr = 1
  LEFT JOIN Orders I2 ON O.OrderNbr = I2.OrderNbr AND I1.LoadNbr = 2
)

SELECT  *,
        'Ratio01' = Quantity01 / TotalQty * 100,
        'Ratio02' = Quantity02 / TotalQty * 100
FROM    OrderSummary

曾因生产数据中毫秒级差异的日期字段出现重复行，目前用DISTINCT可解决，但实现方式不够优雅。

问题 #

当前通过自连接实现多行转单行的方式是否最优？是否有更优方法实现该需求？需注意：无法控制源数据格式，必须返回单行结果给调用应用。

补充说明：同一订单的Status、CustomerID、OrderNbr等字段值一致，仅LoadNbr、Product、Quantity为每行唯一值，8种产品对应8行数据需合并为单行。

优化方案 #

自连接的方式并非最优解，当产品数量达到8种时，多次自连接会导致逻辑冗余、性能下降（需多次扫描同一张表）。更优的方案是采用透视（PIVOT）+ 窗口函数的组合，一次性完成行转列、总量计算和占比统计，逻辑更简洁，性能更稳定。

具体实现（以SQL Server为例） #

WITH OrderDetails AS (
    -- 计算每个订单的总数量，同时保留明细数据
    SELECT 
        OrderStatus,
        CustomerID,
        OrderNbr,
        LoadNbr,
        Product,
        Quantity,
        -- 窗口函数快速计算当前订单的总数量
        TotalQty = SUM(Quantity) OVER (PARTITION BY OrderNbr)
    FROM Orders
),
PivotedData AS (
    -- 通过CASE表达式实现行转列，将不同LoadNbr的产品和数量转为列
    SELECT 
        OrderStatus,
        CustomerID,
        OrderNbr,
        TotalQty,
        Prod01 = MAX(CASE WHEN LoadNbr = 1 THEN Product END),
        Quantity01 = MAX(CASE WHEN LoadNbr = 1 THEN Quantity END),
        Prod02 = MAX(CASE WHEN LoadNbr = 2 THEN Product END),
        Quantity02 = MAX(CASE WHEN LoadNbr = 2 THEN Quantity END),
        Prod03 = MAX(CASE WHEN LoadNbr = 3 THEN Product END),
        Quantity03 = MAX(CASE WHEN LoadNbr = 3 THEN Quantity END),
        Prod04 = MAX(CASE WHEN LoadNbr = 4 THEN Product END),
        Quantity04 = MAX(CASE WHEN LoadNbr = 4 THEN Quantity END),
        Prod05 = MAX(CASE WHEN LoadNbr = 5 THEN Product END),
        Quantity05 = MAX(CASE WHEN LoadNbr = 5 THEN Quantity END),
        Prod06 = MAX(CASE WHEN LoadNbr = 6 THEN Product END),
        Quantity06 = MAX(CASE WHEN LoadNbr = 6 THEN Quantity END),
        Prod07 = MAX(CASE WHEN LoadNbr = 7 THEN Product END),
        Quantity07 = MAX(CASE WHEN LoadNbr = 7 THEN Quantity END),
        Prod08 = MAX(CASE WHEN LoadNbr = 8 THEN Product END),
        Quantity08 = MAX(CASE WHEN LoadNbr = 8 THEN Quantity END)
    FROM OrderDetails
    GROUP BY OrderStatus, CustomerID, OrderNbr, TotalQty
)
-- 计算各产品数量占比，输出最终结果
SELECT 
    OrderStatus,
    CustomerID,
    OrderNbr,
    Prod01,
    Quantity01,
    Prod02,
    Quantity02,
    Prod03,
    Quantity03,
    Prod04,
    Quantity04,
    Prod05,
    Quantity05,
    Prod06,
    Quantity06,
    Prod07,
    Quantity07,
    Prod08,
    Quantity08,
    -- 处理除零错误，保留1位小数
    Ratio01 = CASE WHEN TotalQty > 0 THEN ROUND((Quantity01 * 100.0) / TotalQty, 1) ELSE 0 END,
    Ratio02 = CASE WHEN TotalQty > 0 THEN ROUND((Quantity02 * 100.0) / TotalQty, 1) ELSE 0 END,
    Ratio03 = CASE WHEN TotalQty > 0 THEN ROUND((Quantity03 * 100.0) / TotalQty, 1) ELSE 0 END,
    Ratio04 = CASE WHEN TotalQty > 0 THEN ROUND((Quantity04 * 100.0) / TotalQty, 1) ELSE 0 END,
    Ratio05 = CASE WHEN TotalQty > 0 THEN ROUND((Quantity05 * 100.0) / TotalQty, 1) ELSE 0 END,
    Ratio06 = CASE WHEN TotalQty > 0 THEN ROUND((Quantity06 * 100.0) / TotalQty, 1) ELSE 0 END,
    Ratio07 = CASE WHEN TotalQty > 0 THEN ROUND((Quantity07 * 100.0) / TotalQty, 1) ELSE 0 END,
    Ratio08 = CASE WHEN TotalQty > 0 THEN ROUND((Quantity08 * 100.0) / TotalQty, 1) ELSE 0 END
FROM PivotedData;

方案优势 #

1. 逻辑简洁易扩展：无需多次自连接，新增产品仅需添加对应的CASE分支，维护成本低。
2. 性能更优：仅需扫描源表1-2次（窗口函数+聚合），而自连接需要扫描N+1次（N为产品数量），数据量越大性能差异越明显。
3. 从根源避免重复行：通过GROUP BY按订单维度聚合，直接消除源数据中微小字段差异导致的重复行，无需依赖DISTINCT。

注意事项 #

• 若使用MySQL、PostgreSQL等其他数据库，语法略有差异，但核心思路一致：用CASE表达式实现透视，窗口函数计算总量。
• 必须处理除零错误：通过CASE判断TotalQty是否大于0，避免出现除以零的异常。
• 若LoadNbr不连续，可先通过ROW_NUMBER() OVER (PARTITION BY OrderNbr ORDER BY LoadNbr)生成连续序号，再用该序号进行透视。

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