如何通过Pivot或其他方式实现订单关联产品后的单条记录输出？

问题分析 #

你的核心问题是products表中同一product_id存在多条有效（订单日期落在生效/终止日期区间内）记录，直接关联会导致订单记录重复，需要将同一产品的多条有效记录转成单条订单记录的多列字段。

原SQL的问题在于：

• 两次关联products表会产生笛卡尔积，同一订单会出现(P1=Primary,P2=Secondary)和(P1=Secondary,P2=Primary)两条重复逻辑的记录
• WHERE条件用OR会导致匹配到无效组合（比如P1有效但P2无效，或者反过来），可能引入过期的产品记录

解决方案 #

先给每个product_id下的有效产品记录分配唯一序号，再通过条件聚合将多行转成多列，确保每个订单只对应一条记录：

完整SQL语句 #

WITH ValidProducts AS (
    SELECT 
        product_id,
        category,
        type,
        partner,
        commission,
        eff_date,
        term_date,
        -- 给同一product_id下的有效记录按type排序分配序号，Primary排1，Secondary排2
        ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY CASE type WHEN 'Primary' THEN 1 WHEN 'Secondary' THEN 2 ELSE 3 END) AS row_num
    FROM products
),
OrderValidProducts AS (
    SELECT 
        o.order_id,
        o.id AS product_id,
        o.order_date,
        o.cost,
        vp.category,
        vp.type,
        vp.partner,
        vp.commission,
        vp.row_num
    FROM orders o
    JOIN ValidProducts vp 
        ON o.id = vp.product_id
        AND o.order_date >= vp.eff_date
        AND o.order_date <= vp.term_date
)
SELECT 
    order_id AS 订单ID,
    product_id AS ID,
    order_date AS 订单日期,
    cost AS 成本,
    MAX(CASE WHEN row_num = 1 THEN category END) AS 类别1,
    MAX(CASE WHEN row_num = 2 THEN category END) AS 类别2,
    MAX(CASE WHEN row_num = 1 THEN type END) AS 类型1,
    MAX(CASE WHEN row_num = 2 THEN type END) AS 类型2,
    MAX(CASE WHEN row_num = 1 THEN partner END) AS 合作方1,
    MAX(CASE WHEN row_num = 2 THEN partner END) AS 合作方2,
    MAX(CASE WHEN row_num = 1 THEN commission END) AS 佣金1,
    MAX(CASE WHEN row_num = 2 THEN commission END) AS 佣金2
FROM OrderValidProducts
GROUP BY order_id, product_id, order_date, cost
ORDER BY order_id;

步骤说明 #

1. ValidProducts CTE：给每个product_id下的记录按type优先级分配序号，确保Primary类型排在第1位，Secondary排在第2位，先完成基础的有效记录标记。
2. OrderValidProducts CTE：关联订单表和有效产品表，只保留订单日期落在产品生效/终止区间内的记录，同时带上之前分配的序号。
3. 条件聚合：按订单维度分组，通过CASE WHEN配合MAX函数，将同一订单下不同序号的产品字段转成对应的列，确保每个订单只生成一条记录。

验证结果 #

执行上述SQL后，会得到你期望的输出格式，每个订单对应一条记录，同一产品的多条有效信息分别映射到类别1/类别2等字段中。

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