如何在SELECT语句中实现条件式动态GROUP BY分组？

我来帮你解决这个条件分组的问题～你的需求是：当FLOW_NUMBER为1时合并分组记录，为2或3时保留每条原始记录，原SQL的GROUP BY FLOW_NUMBER会把1、2、3各自分组，显然不符合要求，下面给你两种可行的实现方式：

方案一：用分组键区分分组/不分组场景 #

核心思路是给不同的FLOW_NUMBER分配不同的分组标识：1的所有记录用同一个标识，2/3的每条记录用唯一标识，这样GROUP BY时就能精准控制分组逻辑。

SELECT 
    LISTAGG(DISTINCT PRODORD, ',') WITHIN GROUP (ORDER BY PRODORD) AS PRODORD,
    SUM(QTY) AS QTY,
    FLOW_NUMBER
FROM (
    SELECT 
        CASE 
            -- 所有FLOW=1的记录用同一个分组键
            WHEN FLOW_NUMBER = 1 THEN 'GROUP_FLOW_1'
            -- FLOW=2/3的每条记录用「行号+FLOW值」生成唯一分组键，确保每条单独成组
            ELSE ROW_NUMBER() OVER (PARTITION BY FLOW_NUMBER ORDER BY PRODORD) || '_' || FLOW_NUMBER
        END AS GROUP_KEY,
        FLOW_NUMBER,
        PRODORD,
        QTY
    FROM (
        -- 这里替换成你的实际表即可
        SELECT 1 AS FLOW_NUMBER, 'A' AS PRODORD, 1 AS QTY FROM DUAL
        UNION ALL
        SELECT 1 AS FLOW_NUMBER, 'B' AS PRODORD, 1 AS QTY FROM DUAL
        UNION ALL
        SELECT 1 AS FLOW_NUMBER, 'C' AS PRODORD, 1 AS QTY FROM DUAL
        UNION ALL
        SELECT 2 AS FLOW_NUMBER, 'A' AS PRODORD, 1 AS QTY FROM DUAL
        UNION ALL
        SELECT 2 AS FLOW_NUMBER, 'B' AS PRODORD, 1 AS QTY FROM DUAL
        UNION ALL
        SELECT 2 AS FLOW_NUMBER, 'C' AS PRODORD, 1 AS QTY FROM DUAL
        UNION ALL
        SELECT 3 AS FLOW_NUMBER, 'A' AS PRODORD, 1 AS QTY FROM DUAL
        UNION ALL
        SELECT 3 AS FLOW_NUMBER, 'B' AS PRODORD, 1 AS QTY FROM DUAL
        UNION ALL
        SELECT 3 AS FLOW_NUMBER, 'C' AS PRODORD, 1 AS QTY FROM DUAL
    ) t1
) t2
GROUP BY GROUP_KEY, FLOW_NUMBER
ORDER BY FLOW_NUMBER, GROUP_KEY;

方案逻辑说明： #

• 对于FLOW_NUMBER=1的记录，统一分配GROUP_FLOW_1作为分组键，GROUP BY时会合并所有该标识的记录，实现你要的分组效果。
• 对于FLOW_NUMBER=2/3的记录，用ROW_NUMBER()生成每个FLOW分组内的唯一行号，拼接FLOW值后得到唯一的分组键，这样每条记录都会单独成为一个分组，保留原始行的结构。

方案二：用UNION ALL拆分查询（更直观） #

如果你的逻辑比较简单，直接把分组和不分组的场景分开查询，再用UNION ALL合并结果，可读性更强：

-- 处理FLOW=1的分组逻辑
SELECT 
    LISTAGG(DISTINCT PRODORD, ',') WITHIN GROUP (ORDER BY PRODORD) AS PRODORD,
    SUM(QTY) AS QTY,
    FLOW_NUMBER
FROM your_table
WHERE FLOW_NUMBER = 1
GROUP BY FLOW_NUMBER

UNION ALL

-- 处理FLOW=2/3的不分组逻辑，直接返回原始记录
SELECT 
    PRODORD,
    QTY,
    FLOW_NUMBER
FROM your_table
WHERE FLOW_NUMBER IN (2, 3)
ORDER BY FLOW_NUMBER, PRODORD;

方案逻辑说明： #

• 第一个查询专门处理FLOW_NUMBER=1的分组，用GROUP BY合并记录。
• 第二个查询直接筛选出FLOW_NUMBER=2/3的记录，不做任何分组，保留原始行。
• 最后用UNION ALL合并两个结果集，再排序得到清晰的输出。

两种方案都能满足你的需求：

• 方案一更灵活，适合后续需要扩展更多条件分组的场景；
• 方案二更直观，代码容易理解和维护，适合简单的条件区分。

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