如何用T-SQL筛选所有成员完成任务的GroupTaskId

表结构 #

CREATE TABLE [GroupTaskMembers]
(
    [GroupTaskId] [uniqueidentifier] NOT NULL,
    [MemberId] [uniqueidentifier] NOT NULL,
    [StartedAt] [datetime2](7) NULL,
    [CompletedAt] [datetime2](7) NULL
)

示例数据 #

需求与现有问题 #

需要筛选出所有成员均完成任务的GroupTaskId（即同一GroupTaskId下所有MemberId的CompletedAt不为NULL），并统计这类分组的数量。根据示例数据，预期结果为GroupB、GroupC。

当前执行的查询仅能得到至少有一个成员完成任务的GroupTaskId，不符合需求：

select GroupTaskId 
from GroupTaskMembers 
where CompletedAt is not null

解决方案 #

方法1：GROUP BY 结合 HAVING 子句 #

这是最直接的实现方式，通过分组后检查每个分组中是否存在CompletedAt为NULL的记录：

-- 筛选符合条件的GroupTaskId
SELECT GroupTaskId
FROM GroupTaskMembers
GROUP BY GroupTaskId
HAVING COUNT(CASE WHEN CompletedAt IS NULL THEN 1 END) = 0;

-- 统计符合条件的分组数量
SELECT COUNT(*) AS CompletedGroupCount
FROM (
    SELECT GroupTaskId
    FROM GroupTaskMembers
    GROUP BY GroupTaskId
    HAVING COUNT(CASE WHEN CompletedAt IS NULL THEN 1 END) = 0
) AS CompletedGroups;

原理：COUNT(CASE WHEN CompletedAt IS NULL THEN 1 END)会统计分组内CompletedAt为NULL的成员数，等于0则说明所有成员都完成了任务。

方法2：关系除法实现（NOT EXISTS） #

关系除法的核心是找出不存在“未完成成员”的分组，用NOT EXISTS实现：

-- 筛选符合条件的GroupTaskId
SELECT DISTINCT gtm.GroupTaskId
FROM GroupTaskMembers gtm
WHERE NOT EXISTS (
    SELECT 1
    FROM GroupTaskMembers gtm2
    WHERE gtm2.GroupTaskId = gtm.GroupTaskId
      AND gtm2.CompletedAt IS NULL
);

-- 统计符合条件的分组数量
SELECT COUNT(DISTINCT gtm.GroupTaskId) AS CompletedGroupCount
FROM GroupTaskMembers gtm
WHERE NOT EXISTS (
    SELECT 1
    FROM GroupTaskMembers gtm2
    WHERE gtm2.GroupTaskId = gtm.GroupTaskId
      AND gtm2.CompletedAt IS NULL
);

原理：对于每个分组，检查是否存在任何成员的CompletedAt为NULL，若不存在则该分组符合条件。

方法3：关系除法实现（EXCEPT） #

通过找出所有分组，减去存在未完成成员的分组，得到符合条件的分组：

-- 筛选符合条件的GroupTaskId
SELECT GroupTaskId FROM GroupTaskMembers
EXCEPT
SELECT GroupTaskId FROM GroupTaskMembers WHERE CompletedAt IS NULL;

-- 统计符合条件的分组数量
SELECT COUNT(*) AS CompletedGroupCount
FROM (
    SELECT GroupTaskId FROM GroupTaskMembers
    EXCEPT
    SELECT GroupTaskId FROM GroupTaskMembers WHERE CompletedAt IS NULL
) AS CompletedGroups;

原理：EXCEPT操作会返回第一个查询中存在但第二个查询中不存在的记录，也就是去掉了有未完成成员的分组，剩下的就是所有成员都完成的分组。

内容的提问来源于stack exchange，提问作者Stas Blade