报错：此处不允许使用序列值，如何同时使用GROUP BY与序列NEXTVAL？

原查询报错「Sequence number not allowed here（此处不允许使用序列值）」的原因是：Oracle不允许在包含GROUP BY的SELECT语句中直接使用序列的NEXTVAL，因为序列值不属于分组列或聚合函数的范畴。

要同时实现分组统计和生成序列ID，需先完成GROUP BY聚合逻辑，再为聚合后的每一行分配序列值。可以通过子查询或CTE（公共表表达式）实现：

方法一：使用CTE #

WITH grouped_data AS (
    SELECT 
        TRUNC(brt.TRANSACTION_DATE) AS TRANSACTION_DATE,
        brt.COST_CENTER,
        COUNT(brt.COST_CENTER) AS COUNT_COST_CENTER,
        SUM(brt.COLLECTION_AMOUNT) AS SUM_COLLECTION_AMOUNT,
        SUM(brt.COMMISSION_AMOUNT) AS SUM_COMMISSION_AMOUNT,
        SUM(brt.SERVICE_FEE) AS SUM_SERVICE_FEE,
        SUM(brt.BANK_INCOME_AMOUNT) AS SUM_BANK_INCOME_AMOUNT
    FROM BCS_RPT_TRANSACTION brt
    WHERE brt.IS_AGGREGATED_C = 0
      AND brt.COST_CENTER = :costCenter
    GROUP BY brt.COST_CENTER, TRUNC(brt.TRANSACTION_DATE)
)
SELECT 
    BCS_RPT_AGGREGATOR_COSTCENTER_seq.nextval AS ID,
    TRANSACTION_DATE,
    COST_CENTER,
    COUNT_COST_CENTER,
    SUM_COLLECTION_AMOUNT,
    SUM_COMMISSION_AMOUNT,
    SUM_SERVICE_FEE,
    SUM_BANK_INCOME_AMOUNT
FROM grouped_data;

方法二：使用子查询 #

SELECT 
    BCS_RPT_AGGREGATOR_COSTCENTER_seq.nextval AS ID,
    TRANSACTION_DATE,
    COST_CENTER,
    COUNT_COST_CENTER,
    SUM_COLLECTION_AMOUNT,
    SUM_COMMISSION_AMOUNT,
    SUM_SERVICE_FEE,
    SUM_BANK_INCOME_AMOUNT
FROM (
    SELECT 
        TRUNC(brt.TRANSACTION_DATE) AS TRANSACTION_DATE,
        brt.COST_CENTER,
        COUNT(brt.COST_CENTER) AS COUNT_COST_CENTER,
        SUM(brt.COLLECTION_AMOUNT) AS SUM_COLLECTION_AMOUNT,
        SUM(brt.COMMISSION_AMOUNT) AS SUM_COMMISSION_AMOUNT,
        SUM(brt.SERVICE_FEE) AS SUM_SERVICE_FEE,
        SUM(brt.BANK_INCOME_AMOUNT) AS SUM_BANK_INCOME_AMOUNT
    FROM BCS_RPT_TRANSACTION brt
    WHERE brt.IS_AGGREGATED_C = 0
      AND brt.COST_CENTER = :costCenter
    GROUP BY brt.COST_CENTER, TRUNC(brt.TRANSACTION_DATE)
) grouped_data;

优化说明 #

原查询中TO_DATE(TO_CHAR(brt.TRANSACTION_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')可以替换为TRUNC(brt.TRANSACTION_DATE)，两者效果相同，但TRUNC函数更高效且代码更简洁。

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