SQL Server："counting"子查询的结果不是数字

Question

我在存储过程中具有以下内容

DECLARE @AssocCount INT;
SET @AssocCount = (SELECT COUNT(Associations.GroupId) AS 'count'
                   FROM Personnel.Persons Persons
                   INNER JOIN Personnel.Associations Associations
                              ON Persons.PersonId = Associations.PersonId
                              AND Associations.GroupId = @GroupID
                              AND GETDATE() BETWEEN Associations.StartDate 
                              AND ISNULL(Associations.EndDate, GETDATE())
                   GROUP BY Associations.PersonId);

我的所有测试看起来每个执行都将在某些罕见的奇数情况下返回0或1或可能的>1。在生产中，我们遇到了一个案例，该案例根本没有发现任何记录要计算。结果似乎不是null。我尝试以下没有成功：

SET @AssocCount = ISNULL((SELECT COUNT(Associations.GroupId) AS 'count'
                          FROM Personnel.Persons Persons
                          INNER JOIN Personnel.Associations Associations
                                     ON Persons.PersonId = Associations.PersonId
                                     AND Associations.GroupId = @GroupID
                                     AND GETDATE() BETWEEN Associations.StartDate AND ISNULL(Associations.EndDate, GETDATE())
                          GROUP BY Associations.PersonId), 0);

我如何检查子查询是否为空结果并胁迫零值？

Answer 1

DECLARE @AssocCount INT = 0;
SELECT  @AssocCount =  COUNT(Associations.GroupId) 
               FROM Personnel.Persons Persons
               INNER JOIN Personnel.Associations Associations
                          ON Persons.PersonId = Associations.PersonId
                          AND Associations.GroupId = @GroupID
                          AND GETDATE() BETWEEN Associations.StartDate 
                          AND ISNULL(Associations.EndDate, GETDATE())
               GROUP BY Associations.PersonId