SQL/SAS新手求助：合并多段WHERE IN查询生成动物颜色报表

嘿，作为SQL/SAS新手，碰到这种重复写相似查询的情况太常见啦！其实咱们只需要用条件聚合就能把这三个查询合并成一段，既减少冗余代码，又提升查询效率。

核心思路 #

通过CASE WHEN语句把颜色归类到你需要的三个组别（黑/棕、白/灰、金/银），然后针对每个组别，用带条件的SUM()函数分别统计猫、狗、鹦鹉的数量。

合并后的查询代码（横向展示结果） #

SELECT
    -- 统计黑/棕组的动物数量
    SUM(CASE WHEN animals_color IN ('black', 'brown') THEN cats ELSE 0 END) AS black_brown_cats,
    SUM(CASE WHEN animals_color IN ('black', 'brown') THEN dogs ELSE 0 END) AS black_brown_dogs,
    SUM(CASE WHEN animals_color IN ('black', 'brown') THEN parrots ELSE 0 END) AS black_brown_parrots,
    -- 统计白/灰组的动物数量
    SUM(CASE WHEN animals_color IN ('white', 'grey') THEN cats ELSE 0 END) AS white_grey_cats,
    SUM(CASE WHEN animals_color IN ('white', 'grey') THEN dogs ELSE 0 END) AS white_grey_dogs,
    SUM(CASE WHEN animals_color IN ('white', 'grey') THEN parrots ELSE 0 END) AS white_grey_parrots,
    -- 统计金/银组的动物数量
    SUM(CASE WHEN animals_color IN ('gold', 'silver') THEN cats ELSE 0 END) AS gold_silver_cats,
    SUM(CASE WHEN animals_color IN ('gold', 'silver') THEN dogs ELSE 0 END) AS gold_silver_dogs,
    SUM(CASE WHEN animals_color IN ('gold', 'silver') THEN parrots ELSE 0 END) AS gold_silver_parrots
FROM animals;

如果更希望按颜色组分行展示（报表可读性更强），可以用分组写法：

SELECT
    CASE
        WHEN animals_color IN ('black', 'brown') THEN '黑/棕组'
        WHEN animals_color IN ('white', 'grey') THEN '白/灰组'
        WHEN animals_color IN ('gold', 'silver') THEN '金/银组'
    END AS color_group,
    SUM(cats) AS total_cats,
    SUM(dogs) AS total_dogs,
    SUM(parrots) AS total_parrots
FROM animals
WHERE animals_color IN ('black', 'brown', 'white', 'grey', 'gold', 'silver')
GROUP BY color_group;

小提示 #

• 第一种写法适合需要把所有组数据放在一行对比的场景；
• 第二种写法每个颜色组占一行，后续做报表排版更灵活；
• 两种写法都只需要扫描一次animals表，比原来三次扫描表的效率高很多，数据量越大，优势越明显。

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