PostgreSQL如何基于指定列求和为视图新增animal分类列

核心思路 #

你需要的是将原表的多列动物数量，通过**行转列（列转行）**操作转为每行对应一类动物，再对每类动物的数量求和即可。不同数据库的行转列语法略有差异，以下给出通用兼容的SQL写法，以及主流数据库的简化写法：

通用SQL实现（兼容所有主流数据库） #

这种写法用UNION ALL拼接三类动物的统计结果，所有数据库都支持：

CREATE VIEW animal_total_view AS
SELECT 
  1 AS id,
  'dogs' AS animal,
  SUM(dog_amount) AS total
FROM animaltable
UNION ALL
SELECT 
  2 AS id,
  'cats' AS animal,
  SUM(cat_amount) AS total
FROM animaltable
UNION ALL
SELECT 
  3 AS id,
  'birds' AS animal,
  SUM(bird_amount) AS total
FROM animaltable;

执行SELECT * FROM animal_total_view即可得到你要求的输出：

新增额外animal分类的方法 #

如果后续要加新的动物分类（比如原表新增rabbit_amount列要统计兔子总数量），只需要在UNION ALL后面新增一段查询逻辑即可，示例：

-- 新增rabbit分类后的视图更新语句
CREATE OR REPLACE VIEW animal_total_view AS
SELECT 
  1 AS id,
  'dogs' AS animal,
  SUM(dog_amount) AS total
FROM animaltable
UNION ALL
SELECT 
  2 AS id,
  'cats' AS animal,
  SUM(cat_amount) AS total
FROM animaltable
UNION ALL
SELECT 
  3 AS id,
  'birds' AS animal,
  SUM(bird_amount) AS total
FROM animaltable
-- 新增的rabbit分类逻辑
UNION ALL
SELECT 
  4 AS id,
  'rabbits' AS animal,
  SUM(rabbit_amount) AS total
FROM animaltable;

主流数据库简化写法（可选） #

如果使用MySQL 8.0+、PostgreSQL、SQL Server等支持VALUES子句行构造的数据库，可以用更简洁的写法，后续加分类只需要在VALUES配置里加一行即可：

-- MySQL 8.0+/PostgreSQL 示例
CREATE VIEW animal_total_view AS
SELECT 
  v.id,
  v.animal,
  CASE v.animal
    WHEN 'dogs' THEN SUM(a.dog_amount)
    WHEN 'cats' THEN SUM(a.cat_amount)
    WHEN 'birds' THEN SUM(a.bird_amount)
  END AS total
FROM animaltable a
CROSS JOIN (
  VALUES 
    (1, 'dogs'),
    (2, 'cats'),
    (3, 'birds')
) AS v(id, animal)
GROUP BY v.id, v.animal;

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