在 SQL 中正确使用 Count() 和 Sum() 吗？

Question

好的，所以我希望我能很好地解释这个问题，因为我觉得这将是一个很难的问题。

我今天正在使用两张桌子。这些看起来像：

@pset table (PersonID int, SystemID int, EntitlementID int, TargetID int)

@Connector table (TargetName varchar(10), fConnector bit)

第一个表存储的记录告诉我，哦，这个人有这个系统，它由这些权利组成，谁有这些目标。有点复杂，但请跟我一起。第二个存储 TargetName，然后存储该目标在我的不太理论的系统中是否有连接器。

我想做的是合并这两个表，以便我可以看到 @pset 中每行的目标标志。正如您将看到的，这将对我以后有所帮助。

如果系统中的每个权利都有一个连接到目标的连接器（对于所有这些权利，标志都是 true），那么我想知道。

所有其他人都应该进入不同的表。

这就是我试图做的，但没有成功。我需要知道我哪里出错了。希望比我更有经验的人能够回答。

-- If the count(123) = 10 (ten rows with SystemID = 123) and the sum = 10, cool.
select pset.*, conn.fConnector from @pset pset
inner join vuTargets vt
on vt.TargetID = pset.TargetID
inner join @conn conn
on  conn.TargetName = vt.TargetName
group by ProfileID, SystemRoleID, EntitlementID, TargetID, fConnector
having count(SystemID) = sum(cast(fConnector as int))
order by ProfileID

不幸的是

-- If the count(123) = 10 (ten rows with SystemID = 123) and the sum <> 10
select pset.*, conn.fConnector from @pset pset
inner join vuTargets vt
on vt.TargetID = pset.TargetID
inner join @conn conn
on  conn.TargetName = vt.TargetName
group by ProfileID, SystemRoleID, EntitlementID, TargetID, fConnector
having count(SystemID) <> sum(cast(fConnector as int))
order by ProfileID

，这些不起作用:(

编辑

这是一个屏幕截图，显示请注意，ProfileID 1599 的 SystemID 为 1126567，但其中一项权利没有连接器！如何将这两行放入第二个查询中？

Answer 1

您的基本问题是您正在尝试汇总两个不同的记录集。
初始集合（SELECT 和 GROUP BY 子句）表示您希望集合 [ProfileId、SystemId、EntitlementId、TargetId、fConnector] 中的每个差异都有一条记录。
第二组（HAVING 子句）表示，对于初始集中的每一行，您希望将其 COUNT 条记录与连接的SUM。但是，由于您要求分组到单个标志，因此这会产生每个标志的单行效果（假设一对一关系） 。实际上，你是在说 - '嘿，这个目标是否有联系？是的，我想要它。

您似乎想要的是汇总到SystemId 值。为此，您需要更改 SELECT 和 GROUP BY 子句以仅包含 [ProfileId, SystemId] 集。这将仅返回所有目标均已“连接”的行（从配置文件和系统键入）。您将无法看到各个权利、目标以及它们是否已连接（您将能够推断它们都已连接/未连接，但是）。

---

编辑：

为了充分披露，以下是您如何获得与原始结果集类似的内容，其中列出了所有 EntitlementId 和 TargetId ：

WITH all_connections as (SELECT pset.ProfileId, pset.SystemRoleId
                         FROM @pset pset
                         INNER JOIN vuTargets vt
                         ON vt.TargetId = pset.TargetId
                         INNER JOIN @conn conn
                         ON conn.TargetName = vt.TargetName
                         GROUP BY pset.ProfileId, pset.SystemRoleId
                         HAVING COUNT(pset.SystemRoleId) 
                                                  = SUM(CAST(fConnector as INT)))

SELECT pset.*
FROM @pset pset
JOIN all_connections conn
ON conn.ProfileId = pset.ProfileId
AND conn.SystemRoleId = pset.SystemRoleId

这应该为您提供 TargetId 的 ProfileId/SystemRoleId 键列表，其中所有 EntitlementId 和TargetId 具有连接（或者，对于并非全部具有连接的情况，将 CTE = 翻转为 <>）。

Answer 2

编辑：修复了我原来的查询，也更新了描述

您可以将其拆分：首先找到 fConnector 为 0 的 TargetID然后查找具有与您找到的目标相同的 PersonID、SystemID 对。然后选择相关数据：（这会查找 PersonID、SystemID 对，其中至少一项权利没有连接到目标的连接器）

with abc as (
    select PersonID, SystemID
    from pset P
    where TargetID in (
        select TargetID
        from vuTargets V join connector C on V.TargetName = C.TargetName
        where C.fConnector = 0
        )
)
select P.PersonID, P.SystemID, P.EntitlementID, P.TargetID, C.fConnector
from pset P
    join abc on ((P.PersonID = abc.PersonID) and (P.SystemID = abc.SystemID))
    join vuTargets V on P.TargetID = V.TargetID
    join connector C on V.TargetName = C.TargetName

用于查找 的查询PersonID、SystemID 对（其中所有权利都有一个到目标的连接器）类似：

with abc as (
    select PersonID, SystemID
    from pset P
    where TargetID in (
        select TargetID
        from vuTargets V join connector C on V.TargetName = C.TargetName
        where C.fConnector = 0
        )
)
select P.PersonID, P.SystemID, P.EntitlementID, P.TargetID, C.fConnector
from 
    pset P
    join abc on ((P.PersonID <> abc.PersonID) or (P.SystemID <> abc.SystemID))
    join vuTargets V on P.TargetID = V.TargetID
    join connector C on V.TargetName = C.TargetName

区别在于与临时表的连接（<> 与<代码>=）。这与零的答案非常相似，但不使用计数或求和。