MS Access按条件筛选不在第二表的非唯一ID记录

需求说明 #

需要从TABLE_IN表中筛选出满足以下任一条件的记录：

• TABLE_OUT表中无相同id的记录
• TABLE_IN记录的日期晚于TABLE_OUT表中所有同id记录的日期

输出字段可包含id、qty（或qty总和）及其他相关字段。

表数据 #

TABLE_IN表 #

TABLE_OUT表 #

预期输出 #

尝试的错误SQL #

SELECT [TABLE_IN].*
  FROM [TABLE_IN]
  LEFT JOIN [TABLE_OUT]
    ON [TABLE_IN].id = [TABLE_OUT].id
 WHERE [TABLE_OUT].id IS NULL
    OR ([TABLE_OUT].date < [TABLE_IN].date 
   AND  [TABLE_IN].id    = [TABLE_OUT].id)

错误输出 #

问题分析 #

原SQL逻辑错误：LEFT JOIN会将TABLE_IN的每条记录与所有同id的TABLE_OUT记录关联，只要存在任意一条TABLE_OUT记录日期早于当前TABLE_IN记录，该TABLE_IN记录就会被保留——即使存在其他同id的TABLE_OUT记录日期晚于它。比如id=1的10.11.2022记录，虽有18.11.2022的TABLE_OUT记录，但因存在15.09.2022的TABLE_OUT记录满足date < TABLE_IN.date，被错误筛选出来。

正确解法 #

方法1：使用NOT EXISTS子查询 #

直接判断当前TABLE_IN记录是否不存在同id且日期不早于它的TABLE_OUT记录，逻辑清晰高效：

SELECT ti.*
FROM TABLE_IN ti
WHERE NOT EXISTS (
    SELECT 1
    FROM TABLE_OUT to
    WHERE to.id = ti.id
    AND to.date >= ti.date
)

方法2：通过子查询获取每个id的最晚OUT日期 #

先统计每个id在TABLE_OUT中的最晚日期，再与TABLE_IN关联比较：

SELECT ti.*
FROM TABLE_IN ti
LEFT JOIN (
    SELECT id, MAX(date) AS max_out_date
    FROM TABLE_OUT
    GROUP BY id
) to_max ON ti.id = to_max.id
WHERE to_max.id IS NULL -- 无对应id的OUT记录
OR ti.date > to_max.max_out_date -- IN日期晚于该id的最晚OUT日期

如需输出qty总和 #

若需按id分组输出qty总和，可修改为：

SELECT ti.id, SUM(ti.qty) AS total_qty
FROM TABLE_IN ti
WHERE NOT EXISTS (
    SELECT 1
    FROM TABLE_OUT to
    WHERE to.id = ti.id
    AND to.date >= ti.date
)
GROUP BY ti.id

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