如何在MS Access中按Id获取各列最新值并合并为单行？

原始数据表 #

Id       Year       Month         F1         F2         F3         F4
----------------------------------------------------------------------
1        2020         1           A          B          1          2
1        2020         2           AA      
1        2020         3                      BB         11
2        2020         1                                 3          4
2        2020         2           F          G 

期望结果 #

Id       F1         F2         F3         F4
-----------------------------------------------
1        AA         BB         11         2
2        F          G          3          4

实现查询语句 #

将下面语句中的YourTableName替换为你的实际表名即可执行：

SELECT 
    t.Id,
    (SELECT TOP 1 F1 FROM YourTableName WHERE Id = t.Id AND F1 IS NOT NULL ORDER BY Year DESC, Month DESC) AS F1,
    (SELECT TOP 1 F2 FROM YourTableName WHERE Id = t.Id AND F2 IS NOT NULL ORDER BY Year DESC, Month DESC) AS F2,
    (SELECT TOP 1 F3 FROM YourTableName WHERE Id = t.Id AND F3 IS NOT NULL ORDER BY Year DESC, Month DESC) AS F3,
    (SELECT TOP 1 F4 FROM YourTableName WHERE Id = t.Id AND F4 IS NOT NULL ORDER BY Year DESC, Month DESC) AS F4
FROM YourTableName AS t
GROUP BY t.Id;

思路说明 #

• 每个子查询针对单独一列，筛选出对应Id下该列不为空的记录；
• 通过ORDER BY Year DESC, Month DESC确保按时间从新到旧排序，取第一条（TOP 1）就是该列的最新值；
• 主查询通过GROUP BY t.Id对Id去重，最终合并为每个Id一行的结果。

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