MS Access多表关联分组取首记录并按ID排序的SQL查询问题

问题梳理 #

现有两张表：

1. Materials表：存储物料信息，包含ID、Name、DescriptionNum、SpecificWeight字段
2. MatForTool表：存储需筛选的物料ID列表

需求：

• 关联两张表，获取MatForTool中ID对应的Materials全字段数据
• 按DescriptionNum分组，同组保留第一条记录（按ID排序的第一条）
• 最终结果按ID升序排列
• 尽量简化字段写法，优先可移植方案

原查询问题：
原查询使用GROUP BY DescriptionNum后，无法直接ORDER BY Materials.ID，因为ID未包含在GROUP BY或聚合函数中；同时Access不支持FIRST(*)简化写法。

解决方案1：Access兼容的子查询方案 #

先通过子查询找到每个DescriptionNum对应的最小ID（即同组中ID最小的记录，符合"第一条"要求），再关联Materials表获取全字段，最后排序：

SELECT m.*
FROM Materials m
INNER JOIN (
    -- 子查询：按DescriptionNum分组，取每组最小的ID
    SELECT DescriptionNum, MIN(ID) AS MinID
    FROM Materials
    INNER JOIN MatForTool ON Materials.ID = MatForTool.ID
    GROUP BY DescriptionNum
) AS grouped ON m.ID = grouped.MinID
ORDER BY m.ID;

优势： #

• 完美适配Access环境，无需依赖高级窗口函数
• 直接获取m.*所有字段，不用逐个指定
• 自然支持按ID排序，逻辑清晰

解决方案2：可移植的窗口函数方案（Access 2010+支持） #

使用ROW_NUMBER()窗口函数，给每个DescriptionNum分组的记录按ID排序并编号，取编号为1的记录：

SELECT ID, Name, DescriptionNum, SpecificWeight
FROM (
    SELECT 
        m.*,
        ROW_NUMBER() OVER (PARTITION BY m.DescriptionNum ORDER BY m.ID) AS rn
    FROM Materials m
    INNER JOIN MatForTool ON m.ID = MatForTool.ID
) AS ranked
WHERE rn = 1
ORDER BY ID;

优势： #

• 写法更通用，适配多数现代数据库（如SQL Server、PostgreSQL等）
• 逻辑直观，可灵活调整排序规则（比如改ORDER BY m.Name来取同组名称排序的第一条）
• 若需保留更多字段，只需在外部SELECT中添加对应字段

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