PostgreSQL数据透视与年份分类：如何调整查询输出格式？

要实现按Year列（标记为'Last Year'或'Current Year'）拆分数据、每行对应对应月份销售额的需求，可以通过UNION ALL将去年和今年的聚合结果合并，具体SQL如下：

-- 去年数据行
SELECT 
  Sales_Group,
  'Last Year' AS "Year",
  SUM(Last_Jul) AS Jul_Sales,
  SUM(Last_Aug) AS Aug_Sales
FROM MKT_SALES_DATA
WHERE Sales_Group = 'G1'
GROUP BY Sales_Group

UNION ALL

-- 今年数据行
SELECT 
  Sales_Group,
  'Current Year' AS "Year",
  SUM(Current_Jul) AS Jul_Sales,
  SUM(Current_Aug) AS Aug_Sales
FROM MKT_SALES_DATA
WHERE Sales_Group = 'G1'
GROUP BY Sales_Group;

说明 #

• 第一部分查询聚合去年7、8月的销售额，手动标记Year为'Last Year'
• 第二部分查询聚合今年7、8月的销售额，标记Year为'Current Year'
• UNION ALL将两个结果集合并，最终生成两行数据，每行对应一个年份的月度销售额总和

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