使用SELECT和IN的Oracle SQL查询返回结果不符合预期

原查询的问题 #

你当前的查询会包含所有曾经出现过WRISTRAP或FOOT为Fail的员工，而非仅保留最新一次检测中结果为Fail的员工。比如工牌10001虽然最新检测是全Pass，但因历史记录中有一次WRISTRAP Fail，被错误地包含在结果里。

解决方案：使用窗口函数获取最新记录 #

通过ROW_NUMBER()窗口函数为每个员工的检测记录按时间倒序排序，标记出最新的那条记录，再筛选其中结果为Fail的员工。

完整查询语句 #

WITH LatestWristrap AS (
    SELECT 
        BADGEID_FK,
        WRISTRAP,
        FOOT,
        ROW_NUMBER() OVER (PARTITION BY BADGEID_FK ORDER BY DATEADDED DESC) AS rn
    FROM WRISTRAP
)
SELECT 
    E.BADGEID,
    E.EMP_NAME
FROM EMPLOYEES E
INNER JOIN LatestWristrap LW 
    ON E.BADGEID = LW.BADGEID_FK
WHERE LW.rn = 1
  AND (LW.WRISTrap = 'Fail' OR LW.FOOT = 'Fail');

逻辑说明 #

1. CTE部分：LatestWristrap通过PARTITION BY BADGEID_FK按员工分组，ORDER BY DATEADDED DESC让最新的检测记录排在每组首位，ROW_NUMBER()为每条记录分配序号，最新记录的序号为1。
2. 关联筛选：将员工表与CTE关联，只保留序号为1（最新记录）且WRISTRAP或FOOT为Fail的员工。

验证结果 #

根据提供的测试数据，执行该查询后只会返回工牌10003（James），其最新检测结果中FOOT为Fail，完全符合需求。

替代写法（不使用CTE） #

若环境不支持CTE，可改用子查询实现：

SELECT 
    E.BADGEID,
    E.EMP_NAME
FROM EMPLOYEES E
INNER JOIN (
    SELECT 
        BADGEID_FK,
        WRISTRAP,
        FOOT,
        ROW_NUMBER() OVER (PARTITION BY BADGEID_FK ORDER BY DATEADDED DESC) AS rn
    FROM WRISTRAP
) LW 
    ON E.BADGEID = LW.BADGEID_FK
WHERE LW.rn = 1
  AND (LW.WRISTrap = 'Fail' OR LW.FOOT = 'Fail');

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