如何在PostgreSQL中按相似行聚合后再按列分组？

解决方案SQL #

假设你的表名为user_types，执行以下SQL即可得到目标统计结果：

SELECT
    name,
    SUM(CASE WHEN type LIKE 'api%' THEN 1 ELSE 0 END) AS api,
    SUM(CASE WHEN type LIKE 'notapi%' THEN 1 ELSE 0 END) AS notapi,
    SUM(CASE WHEN type = 'misc' THEN 1 ELSE 0 END) AS misc,
    SUM(CASE WHEN type = 'other' THEN 1 ELSE 0 END) AS other
FROM user_types
GROUP BY name
ORDER BY name;

逻辑说明 #

1. 行转列核心逻辑：利用SUM()聚合函数搭配CASE语句，将每行的type归属判断转换为对应列的计数。符合分组条件的行返回1，否则返回0，求和后就是该分组的总数量。
2. 前缀匹配处理：对于api和notapi前缀的类型，用LIKE 'api%'、LIKE 'notapi%'模糊匹配，能覆盖所有以这些字符串开头的type值（比如api-43149g、notapi-3123这类带后缀的情况）。
3. 固定值分组处理：misc、other是固定的组名，直接用type = 'misc'精确匹配即可。
4. 分组与排序：按name分组聚合，最后按name排序让结果更规整。

执行结果 #

执行后会得到你需要的统计表格：

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