如何在MemSQL中统计无Cancelled状态的唯一ID数量

需求说明 #

需要统计满足以下条件的ID数量：若某ID的StatusType存在Cancelled状态，则该ID不纳入统计；仅统计所有状态均不为Cancelled的ID。根据提供的数据，预期结果为2，对应ID 10004（状态为Available、Not Started）和ID 10006（状态为Not Started）。

数据源 #

ID      StatusType
10001   NULL
10001   Available
10001   Not Started
10001   Cancelled
10002   Cancelled
10002   Not Started
10003   Available
10003   NULL
10003   Cancelled
10004   Available
10004   Not Started
10005   Cancelled
10006   Not Started

解决方案 #

方法1：GROUP BY + HAVING 筛选 #

这种方法通过分组统计每个ID的Cancelled状态出现次数，筛选出次数为0的ID：

SELECT COUNT(DISTINCT ID) AS valid_id_count
FROM your_table_name
GROUP BY ID
HAVING COUNT(CASE WHEN StatusType = 'Cancelled' THEN 1 END) = 0;

逻辑解释

• CASE WHEN StatusType = 'Cancelled' THEN 1 END：仅当状态为Cancelled时返回1，其余情况（包括NULL）返回NULL
• COUNT(...)：只会统计非NULL值，因此这个计数代表该ID的Cancelled状态出现次数
• HAVING COUNT(...) = 0：筛选出没有出现过Cancelled状态的ID
• COUNT(DISTINCT ID)：最终统计符合条件的ID总数

方法2：NOT EXISTS 子查询 #

通过子查询排除存在Cancelled状态的ID：

SELECT COUNT(DISTINCT t1.ID) AS valid_id_count
FROM your_table_name t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE t2.ID = t1.ID
      AND t2.StatusType = 'Cancelled'
);

逻辑解释

• 子查询会找出所有存在Cancelled状态的ID
• NOT EXISTS 筛选出不在子查询结果中的ID，即没有Cancelled状态的ID
• 最后统计这些ID的数量

结果验证 #

两种方法都会返回结果2，对应ID 10004和10006，符合预期需求。

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