SQL如何在带状态条件的统计查询中新增无状态过滤的总计数列

你当前的查询把sc.status IN(1,2)条件写在了EXISTS子句中，会直接过滤掉所有不符合状态要求的记录，无法同时统计全量数据，用条件聚合就可以在一次查询中同时拿到带状态过滤和不带状态过滤的统计值，调整后的SQL如下：

SELECT 
    count(DISTINCT CASE WHEN sc.status IN (1,2) THEN sc.staff END) AS filter_status_staff_cnt,
    count(DISTINCT CASE WHEN sc.status IN (1,2) THEN sc.subgroupId END) AS filter_status_subgroup_cnt,
    count(*) AS no_filter_status_total_cnt
FROM schedules sc
WHERE EXISTS (
    SELECT 1
    FROM schedulegroup sg
    WHERE sg.groupid = 15
      AND sc.subgroupId = sg.subgroup 
      AND sg.created BETWEEN '2022-06-01' AND '2022-06-12' 
)

调整说明 #

• 把原EXISTS子句中属于schedules表的sc.status IN(1,2)条件移出，EXISTS内只保留排班组的过滤规则和两表关联逻辑，保证查询能拉到所有关联到目标排班组的排班记录，不受状态条件限制。
• 原有两个需要按状态过滤的去重统计，通过CASE WHEN做条件判断：只有状态为1、2的记录才会传入COUNT做去重统计，不满足条件的记录返回NULL，会被COUNT函数自动忽略，统计结果和你原查询完全一致。
• 新增的第三列no_filter_status_total_cnt就是不校验状态时符合关联条件的总记录数，如果你需要的是不筛状态的去重员工数/去重分组数，直接对应写count(DISTINCT sc.staff)、count(DISTINCT sc.subgroupId)即可。

注：EXISTS子查询中写SELECT 1是通用规范写法，因为子查询只需要判断存在性，不需要返回实际字段值，和SELECT *效果一致，没有性能差异。

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