基于商品单价分配订单级折扣的SQL实现需求

针对你提出的订单折扣分配需求——多商品按单价比例拆分折扣、单商品全额应用折扣，我们可以利用SQL的窗口函数来实现这个逻辑，下面是具体的实现步骤和代码：

需求回顾 #

• 关联sales_order（订单级）和sales_order_line（商品明细级）两张表
• 订单包含多个商品时：按商品单价占订单总单价的比例分配订单的DISCOUNT_TOTAL
• 订单仅单个商品时：将DISCOUNT_TOTAL全额分配给该商品

表结构与示例数据 #

sales_order表 #

CREATE TABLE sales_order (
    ID VARCHAR(4),
    PLATFORM_ORDER_CODE VARCHAR(8),
    OMS_ORDER_CODE VARCHAR(10),
    DISCOUNT_TOTAL DECIMAL(10,4)
);

INSERT INTO sales_order VALUES
('0001', '19000001', 'nebula0001', 300000),
('0002', '19000001', 'nebula0002', 300000),
('0003', '19000001', 'nebula0003', 300000),
('0004', '19000002', 'nebula0004', 100000),
('0005', '19000002', 'nebula0005', 100000),
('0006', '19000003', 'nebula0006', 50000);

sales_order_line表 #

CREATE TABLE sales_order_line (
    SALES_ORDER_ID VARCHAR(4),
    PLATFORM_ORDER_CODE VARCHAR(8),
    SKU_CODE VARCHAR(8),
    UNIT_PRICE DECIMAL(10,4)
);

INSERT INTO sales_order_line VALUES
('0001', '19000001', 'SKUA0001', 200000),
('0002', '19000001', 'SKUA0002', 100000),
('0003', '19000001', 'SKUA0003', 300000),
('0004', '19000002', 'SKUA0001', 200000),
('0005', '19000002', 'SKUA0002', 100000),
('0006', '19000003', 'SKUA0001', 200000);

实现SQL查询 #

SELECT
    sol.PLATFORM_ORDER_CODE,
    so.OMS_ORDER_CODE,
    sol.SKU_CODE,
    sol.UNIT_PRICE,
    CASE
        -- 单商品订单：全额分配折扣
        WHEN SUM(sol.UNIT_PRICE) OVER (PARTITION BY sol.PLATFORM_ORDER_CODE) = sol.UNIT_PRICE
        THEN so.DISCOUNT_TOTAL
        -- 多商品订单：按单价比例分配折扣
        ELSE ROUND((sol.UNIT_PRICE / SUM(sol.UNIT_PRICE) OVER (PARTITION BY sol.PLATFORM_ORDER_CODE)) * so.DISCOUNT_TOTAL, 4)
    END AS DISCOUNT
FROM sales_order so
JOIN sales_order_line sol ON so.ID = sol.SALES_ORDER_ID
ORDER BY sol.PLATFORM_ORDER_CODE, so.OMS_ORDER_CODE;

代码解释 #

1. 窗口函数SUM(UNIT_PRICE) OVER (PARTITION BY PLATFORM_ORDER_CODE)：计算每个PLATFORM_ORDER_CODE（平台订单号）下所有商品的总单价，既用来判断订单是单商品还是多商品，也作为比例计算的分母。
2. CASE条件判断：
   • 当订单总单价等于当前商品单价时，说明是单商品订单，直接取订单的DISCOUNT_TOTAL作为该商品的折扣。
   • 否则，用当前商品单价除以订单总单价得到占比，再乘以订单折扣总额，最后用ROUND保留4位小数（和你期望的结果格式一致）。
3. JOIN关联：通过sales_order.ID和sales_order_line.SALES_ORDER_ID关联两张表，确保订单和商品明细一一对应。

查询结果 #

执行上述SQL后，会得到你期望的结果：

PLATFORM_ORDER_CODE | OMS_ORDER_CODE | SKU_CODE | UNIT_PRICE | DISCOUNT
---------------------|----------------|----------|------------|----------
19000001             | nebula0001      | SKUA0001 | 200000     | 100000
19000001             | nebula0002      | SKUA0002 | 100000     | 50000
19000001             | nebula0003      | SKUA0003 | 300000     | 150000
19000002             | nebula0004      | SKUA0001 | 200000     | 66666.6666
19000002             | nebula0005      | SKUA0002 | 100000     | 33333.3333
19000003             | nebula0006      | SKUA0001 | 200000     | 50000

内容的提问来源于stack exchange，提问作者H. D. U.