Db2/SQL Server中筛选无对应AM状态的HM状态行的SQL实现

示例数据 #

解决方案（适用于Db2(IBM i)和SQL Server） #

以下几种方法无需临时表，性能优于客户端代码过滤，直接通过SQL实现需求：

方案1：NOT EXISTS子查询（推荐，性能稳定）

SELECT h.ID, h.TICKETNUM, h.DATEREVIEWED, h.STATUS
FROM HISTRY h
WHERE h.STATUS = 'HM'
  AND NOT EXISTS (
    SELECT 1
    FROM HISTRY h_am
    WHERE h_am.TICKETNUM = h.TICKETNUM
      AND h_am.STATUS = 'AM'
  );

逻辑说明：先筛选所有STATUS='HM'的行，再排除同一TICKETNUM下存在STATUS='AM'的记录。NOT EXISTS会在找到匹配的AM记录时立即终止子查询，比NOT IN更可靠（避免NULL值导致的意外结果）。

方案2：LEFT JOIN + 空值过滤

SELECT h.ID, h.TICKETNUM, h.DATEREVIEWED, h.STATUS
FROM HISTRY h
LEFT JOIN HISTRY h_am
  ON h.TICKETNUM = h_am.TICKETNUM
  AND h_am.STATUS = 'AM'
WHERE h.STATUS = 'HM'
  AND h_am.ID IS NULL;

逻辑说明：将HM记录与对应TICKETNUM的AM记录左连接，未匹配到AM记录的行（即h_am.ID IS NULL）即为目标结果。

方案3：窗口函数（适合需额外状态统计的场景）

SELECT ID, TICKETNUM, DATEREVIEWED, STATUS
FROM (
  SELECT 
    h.*,
    COUNT(CASE WHEN h.STATUS = 'AM' THEN 1 END) OVER (PARTITION BY h.TICKETNUM) AS am_count
  FROM HISTRY h
) sub
WHERE sub.STATUS = 'HM'
  AND sub.am_count = 0;

逻辑说明：子查询中用窗口函数统计每个TICKETNUM下AM状态的记录数，外层筛选HM且AM计数为0的行，适合需要同时查看其他状态统计信息的场景。

存储过程封装 #

SQL Server版本

CREATE PROCEDURE GetValidHMRecords
AS
BEGIN
  SET NOCOUNT ON;
  SELECT h.ID, h.TICKETNUM, h.DATEREVIEWED, h.STATUS
  FROM HISTRY h
  WHERE h.STATUS = 'HM'
    AND NOT EXISTS (
      SELECT 1
      FROM HISTRY h_am
      WHERE h_am.TICKETNUM = h.TICKETNUM
        AND h_am.STATUS = 'AM'
    );
END;

Db2(IBM i)版本

CREATE PROCEDURE GetValidHMRecords()
RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE C1 CURSOR WITH RETURN FOR
    SELECT h.ID, h.TICKETNUM, h.DATEREVIEWED, h.STATUS
    FROM HISTRY h
    WHERE h.STATUS = 'HM'
      AND NOT EXISTS (
        SELECT 1
        FROM HISTRY h_am
        WHERE h_am.TICKETNUM = h.TICKETNUM
          AND h_am.STATUS = 'AM'
      );
  OPEN C1;
END;

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