sql-server - 根据子记录更新父表

Question

大家好，我想根据子记录更新父表的状态。

条件是。

Child records having the field Isclosed.

1. 如果所有子记录 Isclosed=1 那么父记录 status=1
2. 如果一些子记录 Isclosed=1 那么父记录 status=2
3. else Parent records status=3

我试过这个:

update Parent set Status=1
where id in(
select ParentID from Child where
Isclosed=1  
 group by ParentID having count(id)=(select count(id)from Child where Parent.id=ParentID))

但它只满足1个条件。

Answer 1

您可以使用 CTE 获取 Child 记录的 COUNT 以及 IsClosed = 1 和每个 Parent 的 COUNT 个 Child 记录。然后使用 CTE 的结果来UPDATE Parent 记录的状态:

WITH Cte AS(
    SELECT
        p.Id,
        ClosedCount = SUM(CASE WHEN c.IsClosed = 1 THEN 1 ELSE 0 END),
        TotalCount = COUNT(*)
    FROM Parent p
    INNER JOIN Child c
        ON c.ParentId = p.Id
    GROUP BY p.Id
)
UPDATE p
    SET p.Status =
        CASE
            WHEN c.ClosedCount = c.TotalCount THEN 1
            WHEN c.ClosedCount = 0 THEN 3
            ELSE 2          
        END
FROM Parent p
INNER JOIN Cte c
    ON c.Id = p.Id

SQL Fiddle