PostgreSQL视图如何引入带UNION ALL子查询的animal与total字段

你可以将子查询作为公共表表达式（CTE）嵌入到视图创建语句中，和原有表关联后获取需要的animal和total字段，修改后的视图创建语句如下：

CREATE VIEW animal_report 
AS
WITH animal_stats AS (
    -- 你提供的分类统计子查询
    SELECT animal, SUM(total) AS total
    FROM (
        SELECT 'dogs' animal, dog_amount AS total FROM animal_table
        UNION ALL 
        SELECT 'cats' animal, cat_amount AS total FROM animal_table
        UNION ALL
        SELECT 'birds' animal, bird_amount AS total FROM animal_table
    ) dt
    GROUP BY animal
)
SELECT 
    ROW_NUMBER() OVER() AS id,
    -- 此处保留你需要的所有animal_location表字段
    animal_stats.animal AS animal_category,
    animal_stats.total AS total
FROM
    animal_location
LEFT JOIN 
    animal_table ON animal_location.id = animal_table.id
-- 可根据业务需求调整关联方式，需要全量匹配统计结果用CROSS JOIN，有对应关联条件则换为LEFT JOIN
CROSS JOIN animal_stats
-- 原GROUP BY语法不符合PostgreSQL规则，需补充所有非聚合查询字段，示例如下，可根据实际聚合需求调整
-- GROUP BY animal_location.id, animal_stats.animal, animal_stats.total

• 注意修正表名拼写：如果子查询中的animaltable和你原有关联的animal_table是同一个表，要统一表名避免执行报错
• 关联逻辑可调整：上述示例使用CROSS JOIN将每个动物类别的统计结果和原有表记录关联，如果你有自定义的关联匹配规则，可以替换为LEFT JOIN加对应关联条件
• 分组规则修正：PostgreSQL要求GROUP BY子句必须包含所有SELECT中未被聚合的字段，原有语句仅按animal_category分组不符合语法要求，你可以根据实际聚合需求补充分组字段，或者删除不需要的分组逻辑

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