在SELECT查询中添加总计行：请协助编写PL/SQL查询转换数据

需要将原始数据转换为每个分组下追加组内汇总行的格式。你之前的查询未生效，原因是没有控制排序逻辑，导致汇总行和明细行混乱排列。

你尝试的原查询代码： #

with t1 as
(select complex, sum(manpower) as tot_manpower from test1_tab group by complex)
select complex, manpower from test1_tab
union all
select complex, tot_manpower from t1

修正后的查询方案： #

通过添加排序字段，确保每个complex的明细行在前，组内汇总行在后：

WITH combined_data AS (
    -- 明细行，标记排序优先级为1（靠前）
    SELECT complex, manpower, 1 AS sort_order
    FROM test1_tab
    UNION ALL
    -- 组内汇总行，标记排序优先级为2（靠后）
    SELECT complex, SUM(manpower) AS manpower, 2 AS sort_order
    FROM test1_tab
    GROUP BY complex
)
SELECT complex, manpower
FROM combined_data
ORDER BY complex, sort_order;

如果需要更简洁的写法，也可以用GROUP BY ROLLUP实现（该写法会额外生成全局总计行）：

SELECT 
    complex,
    SUM(manpower) AS manpower
FROM test1_tab
GROUP BY ROLLUP(complex)
ORDER BY complex NULLS LAST;

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