MS Access SQL查询需求：筛选已签署项目发票的最新记录

第一步：筛选所有记录均已签署的Project+Invoice组合 #

核心思路是排除存在未签署记录的组合——也就是那些Project+Invoice下有任意一条记录的Signed和Date都为空的组合。用NOT EXISTS子查询可以高效实现：

SELECT DISTINCT Project, Invoice
FROM YourTableName
WHERE NOT EXISTS (
    SELECT 1
    FROM YourTableName AS t2
    WHERE t2.Project = YourTableName.Project
      AND t2.Invoice = YourTableName.Invoice
      -- 匹配未签署的记录：Signed和Date都为空
      AND (t2.Signed IS NULL AND t2.[Date] IS NULL)
)

这里用DISTINCT确保每个Project+Invoice组合只出现一次，后续可以基于这个结果进一步处理。

第二步：获取每个合格组合的最大日期记录 #

把上面的筛选逻辑嵌套进分组子查询，先算出每个合格组合的最大日期，再关联原表拿到对应记录：

SELECT t1.*
FROM YourTableName AS t1
INNER JOIN (
    SELECT 
        Project, 
        Invoice, 
        MAX([Date]) AS MaxDate
    FROM YourTableName
    WHERE NOT EXISTS (
        SELECT 1
        FROM YourTableName AS t2
        WHERE t2.Project = YourTableName.Project
          AND t2.Invoice = YourTableName.Invoice
          AND (t2.Signed IS NULL AND t2.[Date] IS NULL)
    )
    GROUP BY Project, Invoice
) AS t2 
    ON t1.Project = t2.Project 
    AND t1.Invoice = t2.Invoice 
    AND t1.[Date] = t2.MaxDate

注意：Date是Access的保留字，必须用方括号[Date]包裹，否则会触发语法错误。

如果同一个组合里有多个记录的日期等于最大日期，这个查询会返回所有匹配的记录。如果需要只取其中一条，可以在主查询里用TOP 1结合排序，比如：

SELECT TOP 1 t1.*
FROM YourTableName AS t1
INNER JOIN (
    -- 同上的分组子查询
    SELECT Project, Invoice, MAX([Date]) AS MaxDate
    FROM YourTableName
    WHERE NOT EXISTS (...)
    GROUP BY Project, Invoice
) AS t2 ON t1.Project = t2.Project AND t1.Invoice = t2.Invoice AND t1.[Date] = t2.MaxDate
ORDER BY t1.Signed DESC -- 按签署状态排序，优先取已明确签署的记录

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