如何在MS Access大表中有序替代LAST()函数实现需求

表结构 #

Asset表 #

主键：Key

Transaction表 #

复合主键：(Key, Date2, TType1, TType2, TType3)

核心需求 #

完成以下操作并最终求和FieldOfInterest：

1. 以Key为关联键，关联Asset与Transaction表
2. 过滤条件：
   • Asset.Date1 ≥ InputDate1
   • Transaction.Date2 ≤ InputDate2
   • Asset.AType = 'T1'
3. 对每个Key，选取Date2、TType1、TType2、TType3值最大的Transaction记录（即复合主键排序后的最后一条）

问题背景 #

• 原使用LAST()函数的查询在Access版本升级后无法可靠返回最新事务记录
• 尝试排名查询（如RANK()/ROW_NUMBER()）因Transaction表有80万行，性能极差

高效解决方案 #

利用Transaction表的复合主键特性，先分组获取每个符合条件Key对应的最大复合主键组合，再关联回Transaction表获取完整记录——此方式避免全表扫描和大规模排序，性能更优。

最终查询语句（带参数） #

PARAMETERS InputDate1 DateTime, InputDate2 DateTime;
SELECT 
    SUM(t.FieldOfInterest) AS TotalFieldOfInterest
FROM 
    TRANSACTION t
INNER JOIN 
    (
        -- 子查询：获取每个Key对应的最大事务主键组合
        SELECT 
            t.Key,
            MAX(t.Date2 & "|" & t.TType1 & "|" & t.TType2 & "|" & t.TType3) AS MaxTransactionKey
        FROM 
            TRANSACTION t
        INNER JOIN 
            Asset a ON t.Key = a.Key
        WHERE 
            a.Date1 >= [InputDate1] 
            AND t.Date2 <= [InputDate2] 
            AND a.AType = 'T1'
        GROUP BY 
            t.Key
    ) sub ON 
        t.Key = sub.Key 
        AND t.Date2 & "|" & t.TType1 & "|" & t.TType2 & "|" & t.TType3 = sub.MaxTransactionKey

性能优化建议 #

1. 确保Transaction表的复合主键(Key, Date2, TType1, TType2, TType3)已启用（主键默认是唯一索引）
2. 为Asset表创建复合索引(AType, Date1, Key)，加速过滤和关联操作
3. 使用参数查询代替硬编码日期，提升复用性的同时优化Access的查询计划

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