如何在Access数据库中不使用子查询重写Invoices表查询语句

实现思路 #

你可以通过分组聚合+JOIN的方式替代原语句的逐行相关子查询，完全符合Access SQL的语法支持，性能提升非常明显：

1. 先对Type>3的发票记录按PPID、Invoice分组，用HAVING COUNT(*) = 1筛选出仅存在1条匹配记录的分组，同时直接取到分组内唯一的AID作为PaymentID
2. 再将聚合结果和交易侧记录（Type<4且不等于2的有效记录）做关联，即可得到符合要求的结果

重写后的SQL语句 #

SELECT
    agg.PaymentID,
    I1.AID AS ChargeID,
    I1.Amount / 100 AS Amount,
    I1.Invoice
FROM
    (
        SELECT 
            PPID, 
            Invoice, 
            MIN(AID) AS PaymentID
        FROM Invoices
        WHERE Type > 3
        GROUP BY PPID, Invoice
        HAVING COUNT(*) = 1
    ) AS agg
INNER JOIN Invoices I1
    ON agg.PPID = I1.PPID 
    AND agg.Invoice = I1.Invoice
WHERE
    I1.Type <> 2 
    AND I1.Type < 4 
    AND I1.Amount > 0
    -- 测试时可加过滤条件：AND I1.PPID = 2250

逻辑说明 #

• 你之前的写法出现重复记录，核心原因是没有提前过滤多匹配的分组，直接JOIN后Type>3的2条记录和Type<4的2条记录生成了笛卡尔积，最终返回4条重复数据
• 聚合后已经保证每个PPID+Invoice分组只有1条Type>3的记录，所以MIN(AID)和你原语句的TOP 1 AID效果完全一致，没有逻辑差异
• 如果你的Access环境对派生表支持不好，也可以把内层的聚合查询单独存为一个Access查询对象（比如命名为SinglePaymentGroup），然后直接关联这个查询对象，连派生表都不用写，性能还会更好
• 要进一步提升性能可以给Invoices表创建(PPID, Invoice, Type, AID, Amount)的联合索引，能让查询直接走索引覆盖，无需回表扫全量数据

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