MS Access多分组取Top1计数结果的SQL查询问题

我正在制作报表，需要汇总每周各站点（SITE）的夜班操作员（NOPER）信息。替班人员第一天工作时报表会报错，目前我能统计每个SORTWEEK周期内各SITE中每位操作员的工作天数，但没法筛选出每个SITE每周工作天数最多的操作员。

当前可用的统计SQL #

SELECT [All].SORTWEEK, [All].SITE, [All].NOPER, COUNT([All].NOPER)
FROM [All]
GROUP BY [All].SORTWEEK, [All].SITE, [All].NOPER
ORDER BY [All].SORTWEEK, [All].SITE, COUNT(NOPER) DESC;

查询结果 #

失败的尝试语句 #

尝试语句1（MS Access报错）

SELECT [All].SITE, [All].SORTWEEK, (SELECT TOP 1 NOPER FROM 
(SELECT [All].NOPER, COUNT([All].[NOPER])
    FROM [All] AS [Temp] 
    WHERE [Temp].[SITE] = [All].[SITE] 
    AND [All].[SORTWEEK] = [Temp].[SORTWEEK]
    ORDER BY COUNT([All].NOPER) DESC)) AS TOPNOP
FROM [All]
GROUP BY SORTWEEK, SITE, TOPNOP
ORDER BY [All].SITE, [All].SORTWEEK, [All].NOPER;

尝试语句2（仅手动输入参数有效，无法批量获取结果）

SELECT [All].SITE, [All].SORTWEEK, 
    (SELECT TOP 1 NOPER FROM 
        (SELECT [All].NOPER, COUNT(NOPER)
        FROM [All]
        WHERE [Temp].SORTWEEK = [All].SORTWEEK
        AND [Temp].SITE = [All].SITE
        GROUP BY [All].SORTWEEK, [All].SITE, [All].NOPER
        ORDER BY COUNT([All].NOPER) DESC) 
    ) AS [#Temp]
FROM [All]
WHERE [Temp].SORTWEEK = [All].SORTWEEK
AND [Temp].SITE = [All].SITE
GROUP BY [All].SORTWEEK, [All].SITE
ORDER BY [All].SITE, [All].SORTWEEK;

正确的MS Access SQL写法 #

方法1：先统计再关联筛选最大值

先统计每位操作员的工作天数，再获取每个(SORTWEEK,SITE)组的最大天数，最后关联筛选出符合条件的记录：

SELECT t.SORTWEEK, t.SITE, t.NOPER, t.Cnt
FROM (
    SELECT [All].SORTWEEK, [All].SITE, [All].NOPER, COUNT([All].NOPER) AS Cnt
    FROM [All]
    GROUP BY [All].SORTWEEK, [All].SITE, [All].NOPER
) AS t
INNER JOIN (
    SELECT SORTWEEK, SITE, MAX(Cnt) AS MaxCnt
    FROM (
        SELECT [All].SORTWEEK, [All].SITE, COUNT([All].NOPER) AS Cnt
        FROM [All]
        GROUP BY [All].SORTWEEK, [All].SITE, [All].NOPER
    ) AS sub
    GROUP BY SORTWEEK, SITE
) AS max_t
ON t.SORTWEEK = max_t.SORTWEEK 
AND t.SITE = max_t.SITE 
AND t.Cnt = max_t.MaxCnt
ORDER BY t.SORTWEEK, t.SITE;

方法2：使用HAVING子查询直接筛选

通过子查询获取当前组的最大工作天数，用HAVING子句匹配筛选：

SELECT [All].SORTWEEK, [All].SITE, [All].NOPER, COUNT([All].NOPER) AS Cnt
FROM [All]
GROUP BY [All].SORTWEEK, [All].SITE, [All].NOPER
HAVING COUNT([All].NOPER) = (
    SELECT MAX(sub.Cnt)
    FROM (
        SELECT COUNT([All].NOPER) AS Cnt
        FROM [All] AS sub
        WHERE sub.SORTWEEK = [All].SORTWEEK 
          AND sub.SITE = [All].SITE
        GROUP BY sub.NOPER
    ) AS sub
)
ORDER BY [All].SORTWEEK, [All].SITE;

两种方法均支持批量获取所有站点每周的结果，若存在多个操作员工作天数相同且为最大值，会一并显示。

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