SQL查询实现双JOIN并获取活动状态名称及各状态统计需求

我来帮你优化查询，一次性满足两个需求：获取活动对应的状态名称，以及按状态统计活动数量并生成对应列。核心是用条件聚合的方式，让数据库直接返回你要的结构化结果。

最终SQL查询 #

SELECT
    milestone.id,
    milestone.initiative_id,
    milestone.created,
    milestone.modified,
    status.name AS "statusName", -- 里程碑自身的状态名称
    COUNT(activity.id)::int AS "activityCount", -- 该里程碑下的总活动数
    MAX(activity.end_on) AS "activityMaxEndOn", -- 最晚结束的活动时间
    milestone.name,
    milestone.description,
    -- 按活动状态分组统计，替换成你系统里实际的状态名称即可
    COUNT(CASE WHEN activity_status.name = '已完成' THEN 1 END)::int AS "countCompleted",
    COUNT(CASE WHEN activity_status.name = '进行中' THEN 1 END)::int AS "countInProgress",
    COUNT(CASE WHEN activity_status.name = '未开始' THEN 1 END)::int AS "countNotStarted"
FROM milestone
INNER JOIN status ON status.id = milestone.status -- 关联里程碑的状态表
LEFT JOIN activity ON activity.milestone_id = milestone.id -- 关联里程碑下的所有活动
LEFT JOIN status AS activity_status ON activity.status_id = activity_status.id -- 关联活动的状态表，拿到状态名称
GROUP BY milestone.id, status.name -- 只按里程碑和它自身的状态分组，确保每个里程碑一行结果

关键优化点说明 #

1. 获取活动状态名称：新增了LEFT JOIN status AS activity_status，把活动的状态ID关联到状态表，直接拿到activity_status.name，代替原来的activity.statusID。
2. 按状态统计活动数：用COUNT(CASE WHEN ... THEN 1 END)实现条件统计，每个状态对应一个计数列。如果你的系统有更多活动状态，只需添加对应的COUNT(CASE...)语句就行。
3. 修正分组逻辑：原来的查询把activity.status放进GROUP BY，会导致同一个里程碑因为活动状态不同被拆成多行。现在只按milestone.id和里程碑自身的状态status.name分组，保证每个里程碑只返回一行数据。

动态状态的灵活方案（可选） #

如果你的活动状态是动态变化的，固定写CASE WHEN不够灵活，可以用PostgreSQL的crosstab交叉表功能动态生成列：

-- 先确保tablefunc扩展已安装
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    -- 子查询：拿到每个里程碑的基础数据，以及每个状态的活动数
    'SELECT
        milestone.id,
        milestone.initiative_id,
        milestone.created,
        milestone.modified,
        status.name AS "statusName",
        COUNT(activity.id)::int AS "activityCount",
        MAX(activity.end_on) AS "activityMaxEndOn",
        milestone.name,
        milestone.description,
        activity_status.name AS activity_status_name,
        COUNT(activity.id)::int AS status_count
    FROM milestone
    INNER JOIN status ON status.id = milestone.status
    LEFT JOIN activity ON activity.milestone_id = milestone.id
    LEFT JOIN status AS activity_status ON activity.status_id = activity_status.id
    GROUP BY milestone.id, status.name, activity_status.name
    ORDER BY milestone.id',
    -- 动态获取所有活动状态名称（假设status表用type区分里程碑和活动状态）
    'SELECT DISTINCT name FROM status WHERE type = ''activity'' ORDER BY name'
) AS ct(
    id INT,
    initiative_id INT,
    created TIMESTAMP,
    modified TIMESTAMP,
    statusName VARCHAR,
    activityCount INT,
    activityMaxEndOn TIMESTAMP,
    name VARCHAR,
    description TEXT,
    -- 这里的列要和上面子查询返回的状态名称一一对应
    countCompleted INT,
    countInProgress INT,
    countNotStarted INT
);

这个方案会根据你状态表中的实际活动状态自动生成计数列，不过要注意crosstab的列定义需要和动态返回的状态名称匹配。

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