SQL技术问题：统计全状态为Completed的任务及房屋任务报表

需求说明 #

某家居维护公司数据库包含house_tasks和task_status两张表，需生成房屋任务报表，包含以下字段：

• house_id：房屋ID
• total_tasks：该房屋的总任务数
• completed_tasks：**所有对应状态记录均为'Completed'**的任务数（仅当任务的每一条状态记录都是Completed时，才算完成任务）
• incomplete_tasks：未完成任务数（包含无状态记录、或存在非Completed状态的任务）
  结果需按house_id降序排列。

表结构 #

• house_tasks表：
  • task_id（主键）
  • house_id
  • task_name
• task_status表：
  • id（主键）
  • task_id
  • description
  • task_status（可选值：'Completed'、'In Progress'、NULL）

当前问题 #

现有查询返回结果不符合预期，例如house_id=4的completed_tasks应为0却显示1。当前使用的SQL语句如下：

SELECT
    house_id, 
    COUNT(DISTINCT house_tasks.task_id) AS total_tasks,
    COUNT(DISTINCT CASE WHEN task_status.task_status = 'Completed' THEN house_tasks.task_id END) AS completed_tasks,
    COUNT(DISTINCT CASE WHEN task_status.task_status IS NULL OR task_status.task_status <> 'Completed' THEN house_tasks.task_id END) AS incomplete_tasks
FROM 
    house_tasks
LEFT JOIN 
    task_status ON house_tasks.task_id = task_status.task_id
GROUP BY 
    house_id 
ORDER BY 
    house_id DESC;

问题分析与修正 #

原查询的核心问题是：只要任务存在一条Completed状态记录，就会被计入completed_tasks，但需求要求任务的所有状态记录都必须是Completed才算完成任务。此外，LEFT JOIN会导致一个任务对应多条状态记录时被重复匹配，干扰统计结果。

修正方案1：先标记任务完成状态 #

先对每个任务判断是否满足“所有状态均为Completed”的条件，再关联房屋信息进行统计：

SELECT
    ht.house_id,
    COUNT(ht.task_id) AS total_tasks,
    SUM(ts.is_completed) AS completed_tasks,
    COUNT(ht.task_id) - SUM(ts.is_completed) AS incomplete_tasks
FROM
    house_tasks ht
LEFT JOIN (
    -- 统计有状态记录的任务是否完全完成
    SELECT
        task_id,
        CASE 
            WHEN COUNT(CASE WHEN task_status <> 'Completed' OR task_status IS NULL THEN 1 END) = 0 
                 AND COUNT(task_status) > 0 
            THEN 1 
            ELSE 0 
        END AS is_completed
    FROM task_status
    GROUP BY task_id
    UNION ALL
    -- 处理无状态记录的任务，标记为未完成
    SELECT
        task_id,
        0 AS is_completed
    FROM house_tasks
    WHERE task_id NOT IN (SELECT DISTINCT task_id FROM task_status)
) ts ON ht.task_id = ts.task_id
GROUP BY ht.house_id
ORDER BY ht.house_id DESC;

修正方案2：用聚合函数判断状态范围 #

通过MAX()和MIN()函数判断任务的所有状态是否均为Completed，逻辑更简洁：

SELECT
    ht.house_id,
    COUNT(DISTINCT ht.task_id) AS total_tasks,
    COUNT(DISTINCT CASE 
        WHEN ts.max_status = 'Completed' AND ts.min_status = 'Completed' 
        THEN ht.task_id 
        END) AS completed_tasks,
    COUNT(DISTINCT CASE 
        WHEN ts.max_status IS NULL OR ts.max_status <> 'Completed' OR ts.min_status <> 'Completed' 
        THEN ht.task_id 
        END) AS incomplete_tasks
FROM house_tasks ht
LEFT JOIN (
    SELECT
        task_id,
        MAX(task_status) AS max_status,
        MIN(task_status) AS min_status
    FROM task_status
    GROUP BY task_id
) ts ON ht.task_id = ts.task_id
GROUP BY ht.house_id
ORDER BY ht.house_id DESC;

解释：对于有状态记录的任务，若max_status和min_status均为Completed，说明所有状态都是Completed；无状态记录的任务max_status为NULL，直接计入未完成。

内容的提问来源于stack exchange，提问作者Артём Шепталин