MS Access带OPTION列的重复记录筛选SQL语句问题排查

需求说明 #

需要编写SELECT查询筛选满足以下条件的记录，同时排除OPTION列值为'NO'的记录：

• 同一ID、DATE、INOUT组合的有效记录数（排除OPTION='NO'）>1
• 同一ID、DATE组合的有效记录数（排除OPTION='NO'）>2

示例：ID为5045、日期12-Jul-24的记录仅显示3条（排除OPTION为'NO'的那条）。

测试表结构与数据 #

Absen表 #

MASTERID表 #

现有代码问题 #

当前执行结果包含OPTION='NO'的记录，核心问题：

1. 主查询未过滤自身记录的OPTION值：子查询仅统计符合条件的记录数量，但主查询没有排除当前记录中OPTION='NO'的条目，导致这类记录即使本身不符合要求，只要子查询的统计条件满足（比如ID+DATE的有效记录数>2），就会被纳入结果。
2. 子查询的OR逻辑放大了误选范围：例如那条OPTION='NO'的OUT记录，虽然ID+DATE+INOUT的有效记录数为0，但ID+DATE的有效记录数为3（满足>2），因此OR条件成立，导致该记录被选中。

修正方案 #

1. 在主查询的WHERE子句中直接排除OPTION='NO'的记录；
2. 保留子查询的统计逻辑，确保只筛选出满足数量条件的有效记录。

修正后的SQL代码 #

SELECT a.ID, MASTERID.NAMEID, a.DATE, a.TIME, a.INOUT
FROM ABSEN AS a INNER JOIN MASTERID ON a.ID = MASTERID.ID
WHERE 
    -- 优先排除当前记录OPTION为'NO'的情况
    IIF(a.OPTION IS NULL, '', a.OPTION) <> 'NO'
    AND (
        -- 条件1：同一ID、DATE、INOUT的有效记录数>1
        (SELECT COUNT(*)
         FROM ABSEN AS a2
         WHERE a.ID = a2.ID 
           AND a.DATE = a2.DATE 
           AND a.INOUT = a2.INOUT
           AND IIF(a2.OPTION IS NULL, '', a2.OPTION) <> 'NO'
        ) > 1
        -- 条件2：同一ID、DATE的有效记录数>2
        OR (SELECT COUNT(*)
            FROM ABSEN AS a2
            WHERE a.ID = a2.ID 
              AND a.DATE = a2.DATE
              AND IIF(a2.OPTION IS NULL, '', a2.OPTION) <> 'NO'
        ) > 2
    )
ORDER BY a.ID, a.DATE, a.INOUT;

优化建议（可选） #

使用窗口函数先统计分组数量，再关联原表，提升查询效率：

SELECT a.ID, m.NAMEID, a.DATE, a.TIME, a.INOUT
FROM ABSEN AS a
INNER JOIN MASTERID AS m ON a.ID = m.ID
INNER JOIN (
    SELECT ID, DATE, INOUT,
           COUNT(*) OVER (PARTITION BY ID, DATE) AS DateCount,
           COUNT(*) OVER (PARTITION BY ID, DATE, INOUT) AS DateInOutCount
    FROM ABSEN
    WHERE IIF(OPTION IS NULL, '', OPTION) <> 'NO'
) AS stats ON a.ID = stats.ID AND a.DATE = stats.DATE AND a.INOUT = stats.INOUT
WHERE 
    IIF(a.OPTION IS NULL, '', a.OPTION) <> 'NO'
    AND (stats.DateInOutCount > 1 OR stats.DateCount > 2)
ORDER BY a.ID, a.DATE, a.INOUT;

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