SQL JOIN去重：如何避免订单级折扣因多商品重复累加

当前数据 #

期望数据 #

解决方案 #

方法1：用窗口函数标记订单内首行 #

无需改动原有CTE结构，只需在最终查询阶段通过ROW_NUMBER()窗口函数控制折扣仅出现在每个订单的首行商品记录中：

SELECT
    order_id,
    product_id,
    quantity,
    amount,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY product_id) = 1 
        THEN discount 
        ELSE 0 
    END AS discount
FROM your_cte_final_output;

• PARTITION BY order_id：按订单分组，确保每个订单内的序号独立计算
• ORDER BY product_id：可替换为你需要的排序规则（比如商品金额、加入时间），只要保证每个订单只有一行序号为1即可

方法2：提前处理订单折扣再关联商品表 #

如果折扣是从订单表关联而来，可以先单独提取订单级折扣数据，再和商品表关联时控制折扣展示逻辑：

WITH order_discounts AS (
    -- 提取订单级折扣
    SELECT order_id, discount FROM orders
),
product_details AS (
    -- 给每个订单内的商品添加序号
    SELECT
        order_id,
        product_id,
        quantity,
        amount,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY product_id) AS row_num
    FROM order_products
)
SELECT
    pd.order_id,
    pd.product_id,
    pd.quantity,
    pd.amount,
    CASE WHEN pd.row_num = 1 THEN od.discount ELSE 0 END AS discount
FROM product_details pd
JOIN order_discounts od ON pd.order_id = od.order_id;

补充说明 #

如果你的CTE逻辑复杂，优先选用方法1，仅修改最终SELECT语句中的折扣列计算方式即可，最小化对原有代码的改动。

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