MS Access使用GROUP BY查询获取每个分组首行的实现方案求助

底层执行逻辑说明 #

• 子查询无TOP N限制时，ORDER BY会被Access查询优化器自动忽略，你内层的排序逻辑实际没有生效，分组时还是按表物理存储顺序取数
• GROUP BY执行优先级高于顶层ORDER BY，顶层排序仅作用于分组后的结果集，不会影响分组时FIRST()的取数顺序
• Access的FIRST()函数默认取数据页中物理存储的第一条记录，不会主动按业务排序取数，只有排序后的结果集被固化时才会按排序顺序取首行

正确实现方案 #

方案1：强制子查询保留排序（轻量场景推荐） #

给内层子查询添加TOP 100 PERCENT关键词，强制Access保留排序结果，外层分组即可取到正确的首行：

SELECT t.g1, t.g2, FIRST(t.id) AS first_id, FIRST(t.datetime) AS first_datetime, FIRST(t.name) AS first_name
FROM (
    SELECT TOP 100 PERCENT t1.g1, t1.g2, t2.id, t2.datetime, t3.name 
    FROM ((table1 t1 INNER JOIN table2 t2 ON t1.fld1 = t2.fld1)
    INNER JOIN table3 t3 ON t1.fld2 = t3.fld2)
    ORDER BY t2.datetime, t2.id
) AS t
GROUP BY t.g1, t.g2

方案2：极值关联查询（全场景兼容） #

不依赖FIRST()特性，通过关联极值的方式精准匹配目标行，兼容所有Access版本，逻辑更稳定：

SELECT t1.g1, t1.g2, t2.id, t2.datetime, t3.name
FROM ((table1 t1 INNER JOIN table2 t2 ON t1.fld1 = t2.fld1)
INNER JOIN table3 t3 ON t1.fld2 = t3.fld2)
INNER JOIN (
    -- 取每个分组最小日期对应的最小id
    SELECT t1_inner.g1, t1_inner.g2, MIN(t2_inner.id) AS min_id
    FROM table1 t1_inner 
    INNER JOIN table2 t2_inner ON t1_inner.fld1 = t2_inner.fld1
    INNER JOIN (
        -- 先取每个分组的最小日期
        SELECT t1_min.g1, t1_min.g2, MIN(t2_min.datetime) AS min_datetime
        FROM table1 t1_min 
        INNER JOIN table2 t2_min ON t1_min.fld1 = t2_min.fld1
        GROUP BY t1_min.g1, t1_min.g2
    ) AS dt_min 
    ON t1_inner.g1 = dt_min.g1 
    AND t1_inner.g2 = dt_min.g2 
    AND t2_inner.datetime = dt_min.min_datetime
    GROUP BY t1_inner.g1, t1_inner.g2
) AS id_min 
ON t1.g1 = id_min.g1 
AND t1.g2 = id_min.g2 
AND t2.id = id_min.min_id

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