如何按ProformaInvoiceNum分组生成Main Process列的SQL解决方案

需求说明 #

需要为包含ProcessName和ProformaInvoiceNum的数据集新增Main Process列，规则如下：

• 同一ProformaInvoiceNum分组内存在TOP CUTTING时，该组所有行的Main Process取值为TOP CUTTING
• 分组内无TOP CUTTING但存在Cutting时，取值为Cutting

原始数据 #

目标效果 #

SQL实现方案 #

方法一：窗口函数（推荐，兼容MySQL 8.0+、PostgreSQL、SQL Server等主流数据库） #

SELECT
    ProcessName,
    ProformaInvoiceNum,
    CASE
        WHEN MAX(CASE WHEN ProcessName = 'TOP CUTTING' THEN 1 ELSE 0 END) OVER (PARTITION BY ProformaInvoiceNum) = 1
            THEN 'TOP CUTTING'
        WHEN MAX(CASE WHEN ProcessName = 'Cutting' THEN 1 ELSE 0 END) OVER (PARTITION BY ProformaInvoiceNum) = 1
            THEN 'Cutting'
        ELSE NULL -- 可根据需求补充无匹配时的默认值
    END AS `Main Process`
FROM your_table_name;

方法二：子查询分组预处理（兼容低版本数据库） #

WITH process_groups AS (
    SELECT
        ProformaInvoiceNum,
        CASE
            WHEN EXISTS (SELECT 1 FROM your_table_name t2 WHERE t2.ProformaInvoiceNum = t1.ProformaInvoiceNum AND t2.ProcessName = 'TOP CUTTING')
                THEN 'TOP CUTTING'
            WHEN EXISTS (SELECT 1 FROM your_table_name t2 WHERE t2.ProformaInvoiceNum = t1.ProformaInvoiceNum AND t2.ProcessName = 'Cutting')
                THEN 'Cutting'
            ELSE NULL
        END AS main_process
    FROM your_table_name t1
    GROUP BY ProformaInvoiceNum
)
SELECT
    t.ProcessName,
    t.ProformaInvoiceNum,
    pg.main_process AS `Main Process`
FROM your_table_name t
LEFT JOIN process_groups pg ON t.ProformaInvoiceNum = pg.ProformaInvoiceNum;

方案说明 #

1. 窗口函数方法：通过PARTITION BY按发票号分组，用MAX()函数判断分组内是否存在指定流程，再通过CASE返回对应主流程，无需额外关联，性能更优。
2. 子查询方法：先对每个发票号分组判断主流程，再通过左关联将主流程值映射到原表每一行，兼容不支持窗口函数的旧版数据库。

内容的提问来源于stack exchange，提问作者Ankur Singh Bhandari