如何统计同时包含SEND和REC状态的唯一ID数量

哈哈，你踩了个SQL里超常见的小坑——同一行的Status字段怎么可能同时等于SEND和REC呢？这就是你的查询返回结果不对的核心原因～

要统计同时存在SEND和REC状态的唯一ID，我们需要换个思路：从「ID的整体状态集合」来判断，而不是单条记录的状态。下面给你几种靠谱的解决方案：

方法1：分组统计状态种类数 #

先按ID分组，统计每个ID对应的不同状态数量，筛选出刚好有2种状态的ID（因为Status只有SEND和REC两个值），最后统计这些ID的总数：

SELECT COUNT(DISTINCT ID)
FROM (
    SELECT ID
    FROM Table
    WHERE Date BETWEEN '2022-01-19' AND '2022-01-19'
    GROUP BY ID
    HAVING COUNT(DISTINCT Status) = 2
) AS subquery;

方法2：用EXISTS子查询验证 #

先找到所有当天有SEND记录的ID，再检查这些ID在同一天是否存在对应的REC记录，符合条件的ID就会被统计：

SELECT COUNT(DISTINCT t1.ID)
FROM Table t1
WHERE t1.Date BETWEEN '2022-01-19' AND '2022-01-19'
  AND t1.Status = 'SEND'
  AND EXISTS (
      SELECT 1
      FROM Table t2
      WHERE t2.ID = t1.ID
        AND t2.Date BETWEEN '2022-01-19' AND '2022-01-19'
        AND t2.Status = 'REC'
  );

方法3：自连接关联SEND和REC记录 #

把同一ID的SEND记录和REC记录做连接，最后统计唯一的ID数：

SELECT COUNT(DISTINCT t1.ID)
FROM Table t1
JOIN Table t2 ON t1.ID = t2.ID
WHERE t1.Date BETWEEN '2022-01-19' AND '2022-01-19'
  AND t1.Status = 'SEND'
  AND t2.Date BETWEEN '2022-01-19' AND '2022-01-19'
  AND t2.Status = 'REC';

这三种方法都能得到你预期的结果2，你可以根据自己数据库的数据量和性能需求选择合适的方式～

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