如何将基于子表中一个字段中的值计算总数的5个子查询归为一个更新

debugcn 发表于 Dev

普罗宁

我继承了一个解决方案，其中将存储过程作为后期处理触发，以计算子表中每种类型的公报的总数并更新父表。

如果到父级的子级行数超过2-3000，则查询不是最佳的，导致超时。

似乎可以通过将一种通信类型而不是5种不同的通信类型分组来解决此问题。

我在想类似这样的伪代码：

UPDATE Parent SET Total = query.Total, email = query.email, letter = query.letter, spoken = query.spoken, written = query.written
JOIN ... as query
WHERE Parent.ParentID = @pid

我的直觉是正确的还是SQL Server能够在内部对其进行优化？

添加索引可能是一种选择，但是据我所知，每个子查询都可以命中一个。

存储过程如下所示：

CREATE PROCEDURE [dbo].[UpdateParentTotal]
 @pid int
AS
UPDATE Parent
    -- count Total
set Total = (select count(*) from Child
               where Child.ParentID = Parent.ParentID),
    -- count Type "email" total
    email  = (select count(*) from Child
               where Child.ParentID = Parent.ParentID and Type = 1),
    -- count Type "letter" total
    letter   = (select count(*) from Child
               where Child.ParentID = Parent.ParentID and Type = 2),
    -- count Total for Spoken word
    spoken = (select count(*) from Child
               where Child.ParentID = Parent.ParentID and Type > 99),
    -- count all types of written word
    written = (select count(*) from Child
               where Child.ParentID = Parent.ParentID and ((Type > 1 and Type < 100) or Type is null))
where ParentID = @pid

GO

PS。给这个问题起个名字真的很困难，因为我不确切知道我需要哪种解决方案。

希金斯

您的想法是正确的……您可以将所有这些计算都放到一个查询中，然后连接到该查询。请参阅下面的示例。

UPDATE Parent
SET 
    Total = CountTotal,
    Email = CountEmail
    Letter = CountLetter
    Spoken = CountSpoken
    Written = CountWritten
FROM 
    Parent 
     INNER JOIN 
      (
        SELECT 
            ParentID,
            COUNT(*) CountTotal,
            COUNT(CASE WHEN [Type] = 1 THEN 1 END) AS CountEmail,
            COUNT(CASE WHEN [Type] = 2 THEN 1 END) AS CountLetter,
            COUNT(CASE WHEN [Type] >99 THEN 1 END) AS CountSpoken,
            COUNT(CASE WHEN (([Type] > 1 and [Type] < 100) or [Type] is null)) THEN 1 END) AS CountWritten,
        FROM Child
        GROUP BY ParentID
      ) Child ON 
        Parent.ParentID = Child.ParentID
WHERE Parent.ParentID = @pid