DB2 SQL技术问询：如何按配送时间与状态过滤订单记录

需求 #

仅返回满足以下条件的记录：

• 距离配送日期/时间恰好还有3小时
• 该订单从未进入过LOADING或LOADED状态（只要订单存在任意一条LOADING/LOADED状态的记录，就不返回任何行）

表结构（关联odrstat与tlorder） #

现有查询问题 #

当前查询存在3处关键错误：

SELECT  b.bill_number, b.detail_line_id, b.callname, b.destname, a.trip_number, b.deliver_by, a.status_code
FROM    odrstat a
LEFT JOIN tlorder b ON a.order_id=b.detail_line_id
WHERE a.trip_number = 62886 
AND NOT EXISTS (SELECT 1 
                FROM odrstat c 
                WHERE a.order_id=c.order_id AND a.status_code=c.status_code AND a.trip_number=c.trip_number AND a.zone_id=c.zone_id AND a.leg_id=c.leg_id AND c.status_code LIKE 'LOAD%')
AND HOUR(current timestamp)+3 = HOUR(b.deliver_by)
GROUP BY b.bill_number, b.detail_line_id, b.callname, b.destname, a.trip_number, 

1. NOT EXISTS逻辑错误：添加了a.status_code=c.status_code，导致仅检查当前行的状态，而非整个订单是否存在LOADING/LOADED记录。
2. 时间判断缺陷：仅比较小时数，未考虑日期，会出现跨天匹配错误，也无法精确匹配“恰好3小时”的时间点。
3. GROUP BY语句不完整，存在语法错误。

实际与预期结果 #

• 实际返回：存在LOADING/LOADED状态的订单仍返回了ASSGN状态的记录
• 预期结果：该订单应无任何返回

修正后的查询语句 #

SELECT DISTINCT
    b.bill_number,
    b.detail_line_id,
    b.callname,
    b.destname,
    a.trip_number,
    b.deliver_by,
    a.status_code
FROM odrstat a
JOIN tlorder b ON a.order_id = b.detail_line_id
WHERE a.trip_number = 62886
  -- 精确匹配距离配送时间还有3小时
  AND b.deliver_by = CURRENT_TIMESTAMP + 3 HOURS
  -- 确保该订单无任何LOADING/LOADED状态记录
  AND NOT EXISTS (
      SELECT 1
      FROM odrstat c
      WHERE c.order_id = a.order_id
        AND c.status_code IN ('LOADING', 'LOADED')
  )

关键修正点说明 #

1. NOT EXISTS逻辑优化：移除a.status_code=c.status_code条件，仅通过order_id关联，检查该订单下是否存在LOADING/LOADED状态的记录。
2. 时间条件精确化：使用完整时间戳计算CURRENT_TIMESTAMP + 3 HOURS，避免跨天匹配错误；若需允许一定时间误差（如±5分钟），可调整为：

   AND b.deliver_by BETWEEN CURRENT_TIMESTAMP + 2 HOURS 55 MINUTES AND CURRENT_TIMESTAMP + 3 HOURS 5 MINUTES
   

3. 添加DISTINCT：避免同一订单因多条非LOAD状态记录导致重复返回；也可改用GROUP BY所有选中字段替代DISTINCT。

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