MS Access SQL 按X分组获取Z列最高频ID的实现与错误排查

这段SQL的核心问题是子查询计算的是全表范围的Z列出现次数最大值，没有和外层的X字段做分组关联，最终逻辑变成“筛选全表中Z出现次数等于全局最高值的行”，完全偏离了“每个X分组内单独计算Z频次最高值”的需求。
实际跑一遍写好的查询就能看到偏差：

• 嵌套子查询对所有X、Z组合分组计数后，各组的计数结果为：
  • 123+122：2次
  • 123+123：1次
  • 256+256：1次
  • 256+255：1次
  • 341+400：2次
• 子查询取上述结果的全局最大值为2，外层HAVING COUNT(Sheet1.Z) = 2只会匹配计数为2的两组（123+122、341+400），256分组下的两组计数都是1，根本达不到全局最大值2，因此会被完全漏掉，这就是查询结果不对的根本原因。

方案1：窗口函数实现（适配支持窗口函数的SQL引擎，如MySQL8.0+、PostgreSQL、SQL Server） #

写法简洁、计算效率高，还能灵活处理同频次的取值规则，完全匹配示例输出：

SELECT X, Z
FROM (
    SELECT 
        X, 
        Z,
        -- 按X拆分独立计算分区，在每个X内部按Z出现频次从高到低排名
        -- 同频次下加Z值升序排序，就能实现示例里256组取更小Z值255的效果
        RANK() OVER (PARTITION BY X ORDER BY COUNT(Z) DESC, Z ASC) AS rk
    FROM Sheet1
    GROUP BY X, Z
) t
WHERE rk = 1; -- 只取每个X分区内排名第一的行

方案2：关联子查询实现（适配不支持窗口函数的老版本SQL引擎，如MySQL5.x） #

通过两次分组统计完成计算：

SELECT t1.X, t1.Z
FROM (
    -- 第一步：统计每个X+Z组合的出现次数
    SELECT X, Z, COUNT(Z) AS z_cnt
    FROM Sheet1
    GROUP BY X, Z
) t1
INNER JOIN (
    -- 第二步：单独计算每个X对应的组内最高Z出现次数
    SELECT X, MAX(z_cnt) AS max_cnt
    FROM (
        SELECT X, Z, COUNT(Z) AS z_cnt
        FROM Sheet1
        GROUP BY X, Z
    ) t_inner
    GROUP BY X
) t2 
ON t1.X = t2.X 
  AND t1.z_cnt = t2.max_cnt
  -- 同频次下取最小Z值，和示例输出保持一致
  AND t1.Z = (
      SELECT MIN(Z) 
      FROM (
          SELECT X, Z, COUNT(Z) AS z_cnt
          FROM Sheet1
          GROUP BY X, Z
      ) t3
      WHERE t3.X = t1.X AND t3.z_cnt = t2.max_cnt
  );

注意：

• 如果业务上允许同频次返回所有符合条件的Z值，直接去掉上述代码里匹配最小Z值的关联条件即可。

内容的提问来源于stack exchange，提问作者Juan Espinosa。