SQL查询指定时段重复条码及状态变更异常问题解决

结果不符合预期是查询逻辑缺失导致，不存在语法错误：

• 外层查询未添加时间范围过滤：子查询仅筛选出指定时间段内重复的barcode值，但外层WHERE barcode IN (...)的匹配逻辑会返回该barcode对应的全量历史记录，自然会带出时间区间外的数据。
• 原逻辑未覆盖核心需求规则：仅判断了barcode重复，没有校验「时间段内barcode最新status为1、且历史出现过status为0」的状态变更规则，本身就不满足需求要求。

实现时需要满足三层校验：

1. 所有参与计算、最终返回的记录都必须落在指定时间区间内
2. 对应barcode在时间段内记录数≥2，即重复出现
3. 对应barcode在时间段内最新一条记录的status为1，且时间段内存在status=0的记录，即状态从0变更为1

兼容全SQL版本的通用写法 #

该写法适配MySQL5.x等不支持窗口函数的环境：

SELECT id, name, barcode, status, time_created
FROM table_name
WHERE 
  time_created BETWEEN '2022-07-02 00:00:00' AND '2022-07-04 23:59:59'
  AND barcode IN (
    SELECT barcode
    FROM (
      SELECT 
        barcode,
        SUBSTRING_INDEX(GROUP_CONCAT(status ORDER BY time_created DESC), ',', 1) AS latest_status,
        SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS has_zero_status,
        COUNT(*) AS total_count
      FROM table_name
      WHERE time_created BETWEEN '2022-07-02 00:00:00' AND '2022-07-04 23:59:59'
      GROUP BY barcode
    ) t
    WHERE 
      total_count > 1
      AND latest_status = 1
      AND has_zero_status > 0
  )
ORDER BY barcode, time_created;

支持窗口函数的高性能写法 #

如果使用MySQL8.0+、PostgreSQL、Spark SQL等支持窗口函数的引擎，写法更简洁、执行效率更高：

WITH range_records AS (
  SELECT 
    id, name, barcode, status, time_created,
    FIRST_VALUE(status) OVER (PARTITION BY barcode ORDER BY time_created DESC) AS latest_status,
    MAX(CASE WHEN status = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY barcode) AS has_zero_status,
    COUNT(*) OVER (PARTITION BY barcode) AS total_count
  FROM table_name
  WHERE time_created BETWEEN '2022-07-02 00:00:00' AND '2022-07-04 23:59:59'
)
SELECT id, name, barcode, status, time_created
FROM range_records
WHERE 
  total_count > 1
  AND latest_status = 1
  AND has_zero_status = 1
ORDER BY barcode, time_created;

补充：如果不需要返回符合条件的barcode在时间段内的全量记录，仅需要返回状态变更为1的那条最新记录，在上述SQL最后追加筛选条件，取每个barcode分组下time_created最大的记录即可。

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