Oracle SQL中合并同结构表并添加自定义Branch列的实现方法

直接在每个SELECT语句中新增固定值的Branch列，同时把该列加入对应的GROUP BY子句即可——因为分组时必须包含所有非聚合的输出列。

修改后的完整SQL如下：

SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
       ROUND(SUM(quantity), 2) AS "Quantity",
       ROUND(SUM(value) , 2) AS "ContractValue",
       product AS "Product",
       intention AS "Intention",
       'Optimization Sales' AS "Branch" -- 新增table1对应的Branch固定值
FROM table1
GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, intention, 'Optimization Sales' -- 将Branch固定值加入分组

UNION ALL 

SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
       ROUND(SUM(quantity), 2) AS "Quantity",
       ROUND(SUM(value) , 2) AS "ContractValue",
       product AS "Product",
       intention AS "Intention",
       'Validation Sales' AS "Branch" -- 新增table2对应的Branch固定值
FROM table2
GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, intention, 'Validation Sales' -- 将Branch固定值加入分组

关键注意事项 #

• 剩下的十余张表，只需复制上述结构，替换FROM后的表名和Branch对应的字符串值即可
• 必须保证每个SELECT分支的列数、列顺序、数据类型完全一致，否则UNION ALL会报错
• 因为Branch是固定值，加入GROUP BY不会改变分组结果，只是满足SQL语法要求

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