基于子任务状态更新父任务的PostgreSQL查询需求（含多级场景）

针对仅支持1级父子关系的场景，可通过以下PostgreSQL查询实现需求：当某父任务的所有子任务均已完成时，将该父任务的completed字段设为true。

UPDATE tasks p
SET completed = true
WHERE p.parent_id IS NULL -- 筛选无上级的父任务
AND EXISTS (
    SELECT 1
    FROM tasks c
    WHERE c.parent_id = p.id
    -- 统计子任务总数与已完成子任务数，相等则说明全部完成
    HAVING COUNT(*) = COUNT(CASE WHEN c.completed THEN 1 END)
    AND COUNT(*) > 0 -- 排除无任何子任务的父任务
);

逻辑说明 #

• 先定位所有父任务（parent_id IS NULL的记录）
• 子查询通过COUNT(*)获取该父任务的子任务总数，COUNT(CASE WHEN c.completed THEN 1 END)统计已完成的子任务数量
• 当两个数值相等且子任务数大于0时，说明所有子任务都已完成，此时更新父任务的completed状态为true

针对提供的测试数据，执行该查询后，任务ID 4和11会被设为completed=true，任务ID 2因存在未完成子任务保持false，符合预期。

对于多层级的非循环任务树，需要从最底层的叶子节点向上递归确认所有后代任务的完成状态，再逐层更新父任务。以下提供两种实现方式：

方式1：一次性递归更新所有任务 #

通过递归CTE遍历整个任务树，计算每个任务的所有后代是否都已完成，再批量更新状态：

WITH RECURSIVE task_tree AS (
    -- 基础节点：无后代的叶子任务
    SELECT 
        id,
        parent_id,
        completed,
        completed AS all_descendants_completed
    FROM tasks
    WHERE NOT EXISTS (SELECT 1 FROM tasks c WHERE c.parent_id = tasks.id)
    
    UNION ALL
    
    -- 递归向上遍历父任务，检查所有子任务的后代完成状态
    SELECT 
        p.id,
        p.parent_id,
        p.completed,
        BOOL_AND(t.all_descendants_completed) AS all_descendants_completed
    FROM tasks p
    JOIN task_tree t ON p.id = t.parent_id
    GROUP BY p.id, p.parent_id, p.completed
)
-- 仅更新状态有变化的任务
UPDATE tasks
SET completed = tt.all_descendants_completed
FROM task_tree tt
WHERE tasks.id = tt.id
AND tasks.completed != tt.all_descendants_completed;

逻辑说明 #

• 递归CTE从叶子节点开始，逐层向上计算每个任务的all_descendants_completed字段：只有当所有子任务的后代都已完成时，该字段为true
• 最后对比任务原有completed状态，只更新需要改变的记录，提升执行效率

方式2：触发器实时自动更新 #

如果需要在子任务状态变化时实时同步更新所有上级任务的状态，可以创建触发器实现：

-- 定义触发器函数
CREATE OR REPLACE FUNCTION update_parent_completed()
RETURNS TRIGGER AS $$
BEGIN
    -- 递归找出所有上级任务链
    WITH RECURSIVE parent_chain AS (
        SELECT parent_id FROM tasks WHERE id = NEW.id
        UNION ALL
        SELECT t.parent_id FROM tasks t JOIN parent_chain pc ON t.id = pc.parent_id WHERE t.parent_id IS NOT NULL
    )
    -- 更新每个上级任务的completed状态
    UPDATE tasks p
    SET completed = (
        SELECT BOOL_AND(c.completed)
        FROM tasks c
        WHERE c.parent_id = p.id
    )
    FROM parent_chain pc
    WHERE p.id = pc.parent_id
    AND p.completed != (
        SELECT BOOL_AND(c.completed)
        FROM tasks c
        WHERE c.parent_id = p.id
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器，当completed字段更新时触发
CREATE TRIGGER trigger_update_parent_completed
AFTER UPDATE OF completed ON tasks
FOR EACH ROW
EXECUTE FUNCTION update_parent_completed();

逻辑说明 #

• 当任意任务的completed状态改变时，触发器会递归找出该任务的所有上级任务（父、祖父等）
• 对每个上级任务，检查其所有直接子任务是否都已完成（子任务的状态已包含自身后代的完成情况），并更新上级任务的状态
• 实现了实时同步，无需手动执行更新查询

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