SQL实现指定日期区间内状态始终为running的ID查询

需求规则 #

• 提取指定日期区间内状态始终为running的ID
• 只要区间内对应ID出现过任意非running的状态值，该ID所有记录都不纳入结果集

测试样例 #

测试表数据如下：

基于2022-06-09到2022-06-11的区间查询，预期仅返回ID为98765的所有记录。

原有SQL问题分析 #

原有写法仅做了行级过滤：

SELECT
*
From....
Where status='running' 
AND iddate BETWEEN DATE('2022-06-09') AND DATE('2022-06-11')
Group by 1,2,3

该逻辑只筛选了状态为running的单日记录，没有对ID做全区间状态校验，因此ID12345在06-09、06-10的running记录会被错误返回。

正确SQL写法 #

两种通用实现方式，按需选择即可：

写法1：NOT EXISTS 排除异常ID #

逻辑最直观，直接排除掉区间内出现过非running状态的ID：

SELECT t1.*
FROM 你的业务表 t1
WHERE t1.iddate BETWEEN DATE('2022-06-09') AND DATE('2022-06-11')
  AND NOT EXISTS (
      SELECT 1
      FROM 你的业务表 t2
      WHERE t2.id = t1.id
        -- 同日期区间范围
        AND t2.iddate BETWEEN DATE('2022-06-09') AND DATE('2022-06-11')
        -- 存在非running状态就排除
        AND t2.status != 'running'
  )

写法2：HAVING 聚合校验 #

先按ID分组，校验组内是否全为running状态，再回表取数：

SELECT t.*
FROM 你的业务表 t
WHERE t.iddate BETWEEN DATE('2022-06-09') AND DATE('2022-06-11')
  AND t.id IN (
      SELECT id
      FROM 你的业务表
      WHERE iddate BETWEEN DATE('2022-06-09') AND DATE('2022-06-11')
      GROUP BY id
      -- 组内非running记录数为0，说明全程是running
      HAVING SUM(CASE WHEN status <> 'running' THEN 1 ELSE 0 END) = 0
  )

提示：如果要查询多个不连续的日期区间，只需要把上述SQL里的BETWEEN区间条件替换为多区间的OR逻辑，或者关联提前构建的日期维表做匹配即可，核心校验逻辑无需调整。

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