MS Access 2010中按资产分组提取最新交易记录并添加状态

需求说明 #

按**Asset（资产）**分组，获取交易表中的最新交易记录，同时根据[Checked in Date]是否为空标记资产当前状态（可用/使用中），并保留与[Contacts Extended]表的关联以获取联系人信息。

示例数据 #

交易表（Transactions） #

预期查询输出 #

现有关联查询SQL #

SELECT Transactions.ID, Transactions.Asset, Transactions.[Checked Out To], Transactions.[Checked Out Date], Transactions.[Checked in Date], [Contacts Extended].[Contact Name], Transactions.Notes
FROM Transactions INNER JOIN [Contacts Extended] ON Transactions.[Checked Out To] = [Contacts Extended].ID;

尝试的SQL（存在错误） #

SELECT Transactions.ID, Transactions.Asset, Transactions.[Checked Out To], Transactions.[Checked Out Date], Transactions.[Checked in Date], [Contacts Extended].[Contact Name]
FROM  Transactions inner join (select asset, max([Checked Out Date]) as MaxDate from Transactions group by asset) tm on Transactions.[Asset] = tm.Asset and t.[Checked Out Date] = tm.MaxDate

正确SQL实现方案 #

SELECT 
    t.ID, 
    t.Asset, 
    t.[Checked Out Date] AS [Date Out], 
    t.[Checked in Date] AS [Date In],
    ce.[Contact Name],
    t.Notes,
    -- 根据归还日期是否为空判断资产状态
    IIF(IsNull(t.[Checked in Date]), 'In Use', 'Available') AS 状态
FROM 
    Transactions t
INNER JOIN 
    [Contacts Extended] ce ON t.[Checked Out To] = ce.ID
INNER JOIN 
    (
        -- 子查询获取每个资产的最新借出日期
        SELECT Asset, MAX([Checked Out Date]) AS MaxOutDate
        FROM Transactions
        GROUP BY Asset
    ) tm ON t.Asset = tm.Asset AND t.[Checked Out Date] = tm.MaxOutDate;

逻辑说明 #

1. 子查询tm：按资产分组，计算每个资产的最大[Checked Out Date]（即最新借出日期），用于定位该资产的最新交易记录。
2. 关联主表：通过Asset和[Checked Out Date] = MaxOutDate的条件关联，筛选出每个资产的最新交易条目。
3. 状态判断：使用IIF函数，若[Checked in Date]为空，说明资产未归还，标记为In Use；否则标记为Available。
4. 关联联系人表：保留原有与[Contacts Extended]表的关联逻辑，获取借出联系人的名称信息。

注意事项 #

• 确保[Checked Out Date]字段为日期类型，避免字符串比较导致的逻辑错误。
• 若同一资产在同一日期有多条借出记录，此SQL会返回所有该日期的记录；若需确保仅返回一条，可在子查询中同时使用MAX(ID)或其他唯一标识字段进一步筛选。

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