如何编写SQL按后续行值合并连续行生成采购零件报表

核心思路 #

通过窗口函数为每个自制件（M）及其后续连续的采购件（P）分配唯一组ID，再按组聚合，实现每个自制件对应其关联采购零件的单行记录。

具体实现步骤 #

假设你的表名为order_components，包含字段：order_no（订单编号）、order_line_no（订单行号）、CP_COMP_SEQ（组件序列）、MFG_PURCH_FLG（自制/采购标识）、part_no（零件编号，采购件关键信息）。

1. 生成组ID，划分自制件-采购件组

用累计求和窗口函数，为每个M行及后续连续P行分配同一个组ID：

WITH grouped_components AS (
    SELECT
        order_no,
        order_line_no,
        CP_COMP_SEQ,
        MFG_PURCH_FLG,
        part_no,
        -- 按订单+行号分区，按组件序列排序，累计统计M的数量生成组ID
        SUM(CASE WHEN MFG_PURCH_FLG = 'M' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY order_no, order_line_no ORDER BY CP_COMP_SEQ) AS group_id
    FROM order_components
)

2. 按组聚合，关联自制件与采购件

过滤出自制件行，关联同组采购件并聚合信息：

SELECT
    m.order_no,
    m.order_line_no,
    m.CP_COMP_SEQ AS mfg_comp_seq,
    m.part_no AS mfg_part_no,
    -- 聚合采购零件，不同数据库用对应函数：STRING_AGG（PostgreSQL/SQL Server）、LISTAGG（Oracle）、GROUP_CONCAT（MySQL）
    COALESCE(STRING_AGG(p.part_no, ', '), '无采购零件') AS purch_parts
FROM grouped_components m
LEFT JOIN grouped_components p
    ON m.order_no = p.order_no
    AND m.order_line_no = p.order_line_no
    AND m.group_id = p.group_id
    AND p.MFG_PURCH_FLG = 'P'
WHERE m.MFG_PURCH_FLG = 'M'
GROUP BY m.order_no, m.order_line_no, m.CP_COMP_SEQ, m.part_no
ORDER BY m.order_no, m.order_line_no, m.CP_COMP_SEQ;

方案说明 #

• 组ID生成逻辑：每个M行触发组ID加1，后续连续P行继承当前组ID，直到下一个M行出现，精准划分每个自制件对应的采购件范围，解决你之前无法在标识变更时停止关联的问题。
• 聚合函数适配：根据使用的数据库调整聚合函数，比如Oracle需用LISTAGG(p.part_no, ', ') WITHIN GROUP (ORDER BY p.CP_COMP_SEQ)，MySQL用GROUP_CONCAT(p.part_no ORDER BY p.CP_COMP_SEQ SEPARATOR ', ')。
• 空值处理：用COALESCE将无采购件的情况显示为指定文本，匹配需求中的“无采购零件”场景。

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