如何在MS Access中基于对应最大日期合并多行数据获取最新数值

需要按Employee分组，分别根据每个员工的Max(Date_Number)获取对应的最新Number，同时根据Max(Date_Number2)获取对应的最新Number2。

原始数据 #

期望结果 #

用户尝试过单独创建表查找Max(Date_Number)和Max(Date_Number2)，但无法关联获取对应日期的Number或Number2。

方法1：使用窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

通过ROW_NUMBER()窗口函数分别对两个日期字段按Employee分组降序排序，筛选出每组排名第一的记录后关联，得到目标结果：

WITH number_latest AS (
    SELECT 
        Employee,
        Date_Number,
        Number,
        ROW_NUMBER() OVER(PARTITION BY Employee ORDER BY Date_Number DESC) AS rn_num
    FROM your_table_name
),
number2_latest AS (
    SELECT 
        Employee,
        Date_Number2,
        Number2,
        ROW_NUMBER() OVER(PARTITION BY Employee ORDER BY Date_Number2 DESC) AS rn_num2
    FROM your_table_name
)
SELECT 
    n.Employee,
    n.Date_Number,
    n.Number,
    n2.Date_Number2,
    n2.Number2
FROM number_latest n
JOIN number2_latest n2 ON n.Employee = n2.Employee
WHERE n.rn_num = 1 AND n2.rn_num2 = 1;

方法2：使用子查询关联 #

先获取每个Employee对应的最大日期，再关联原表提取对应数值：

SELECT 
    t1.Employee,
    t1.Date_Number,
    t1.Number,
    t2.Date_Number2,
    t2.Number2
FROM (
    SELECT 
        Employee,
        Date_Number,
        Number
    FROM your_table_name
    WHERE (Employee, Date_Number) IN (
        SELECT Employee, MAX(Date_Number) 
        FROM your_table_name 
        GROUP BY Employee
    )
) t1
JOIN (
    SELECT 
        Employee,
        Date_Number2,
        Number2
    FROM your_table_name
    WHERE (Employee, Date_Number2) IN (
        SELECT Employee, MAX(Date_Number2) 
        FROM your_table_name 
        GROUP BY Employee
    )
) t2 ON t1.Employee = t2.Employee;

注意：需将your_table_name替换为实际表名；若日期字段为字符串类型，确保数据库能正确识别并排序日期，若为日期类型则无需额外处理。

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