PostgreSQL 11：使用string_agg按项目类型与状态聚合项目统计

格式一（包含状态名称） #

WITH project_types AS (
    SELECT id, lbl FROM project_type ORDER BY id
),
status_counts AS (
    SELECT
        pt.id,
        pt.lbl,
        count(CASE WHEN tp.status = 'open' THEN 1 END) as open_cnt,
        count(CASE WHEN tp.status = 'inProgress' THEN 1 END) as inprogress_cnt,
        count(CASE WHEN tp.status = 'closed' THEN 1 END) as closed_cnt
    FROM project_types pt
    LEFT JOIN technical_project tp ON pt.id = tp.project_type
    GROUP BY pt.id, pt.lbl
    ORDER BY pt.id
)
SELECT
    '[' || string_agg(lbl, ',') || ']' ||
    ' , open , [' || string_agg(open_cnt::text, ',') || ']' ||
    ' , inProgress , [' || string_agg(inprogress_cnt::text, ',') || ']' ||
    ' , closed , [' || string_agg(closed_cnt::text, ',') || ']' as result
FROM status_counts;

执行后输出：
[Research,Business,Maintenance], open ,[2,0,0] ,inProgress ,[1,2,0] ,closed ,[0,1,0]

格式二（仅包含类型和统计数组） #

WITH project_types AS (
    SELECT id, lbl FROM project_type ORDER BY id
),
status_counts AS (
    SELECT
        pt.id,
        pt.lbl,
        count(CASE WHEN tp.status = 'open' THEN 1 END) as open_cnt,
        count(CASE WHEN tp.status = 'inProgress' THEN 1 END) as inprogress_cnt,
        count(CASE WHEN tp.status = 'closed' THEN 1 END) as closed_cnt
    FROM project_types pt
    LEFT JOIN technical_project tp ON pt.id = tp.project_type
    GROUP BY pt.id, pt.lbl
    ORDER BY pt.id
)
SELECT
    '[' || string_agg(lbl, ',') || ']' ||
    ' , [' || string_agg(open_cnt::text, ',') || ']' ||
    ' , [' || string_agg(inprogress_cnt::text, ',') || ']' ||
    ' , [' || string_agg(closed_cnt::text, ',') || ']' as result
FROM status_counts;

执行后输出：
[Research,Business,Maintenance] ,[2,0,0] ,[1,2,0] ,[0,1,0]

逻辑说明 #

1. project_types CTE：获取排序后的项目类型列表，确保输出的类型顺序与project_type表的id顺序一致。
2. status_counts CTE：通过左连接关联technical_project表，使用CASE WHEN分别统计每个项目类型在三种固定状态下的项目数量，无匹配项时count返回0。
3. 最终查询：用string_agg将类型名称、各状态统计值拼接成要求的数组格式字符串，组合成最终结果。

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