更新基于子记录的父表格

Question

大家好我想更新父表的状态取决于子记录。

条件是。

子字段Isclosed。

1. 如果所有子记录Isclosed=1然后父记录状态= 1
2. 如果某些子记录Isclosed=1然后父记录状态= 2
3. 其他父记录状态= 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每Parent与IsClosed = 1也得到COUNT S的Child记录COUNT FO 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

Answer 2

试试这个简单的查询，可帮助您

UPDATE P 
SET P.status = (CASE WHEN MaxIsclosed = MinIsclosed AND MaxIsclosed > 0 THEN 1 
        WHEN MaxIsclosed > 0 THEN 2 ELSE 3 END) 
FROM Parent P 
LEFT OUTER JOIN (SELECT PARENTID,MAX(Isclosed) AS MaxIsclosed,MIN(Isclosed) AS MinIsclosed 
    FROM CHILD GROUP BY PARENTID) C ON P.PARENTID = C.PARENTID 

Answer 3

只需经过下面的查询。

UPDATE [Parent] 
SET [Status] = CASE WHEN EXISTS ( SELECT [ParentID] 
             FROM [Child] 
             WHERE [ParentID] = [Parent].[id] 
             GROUP BY [ParentID] 
             HAVING SUM([Isclosed]) = COUNT([ParentID]) 
            ) 
         THEN 1 
         WHEN EXISTS ( SELECT [ParentID] 
             FROM [Child] 
             WHERE [ParentID] = [Parent].[id] 
             GROUP BY [ParentID] 
             HAVING SUM([Isclosed]) = 0 
            ) 
         THEN 3 
         ELSE 2 
        END 

如果需要，可以将[已关闭]设为[已关闭]。