Access SQL/VBA：如何获取各ID下订阅数最大的记录

你之前写的SQL无法得到预期结果，核心问题有两个：

• 分组维度错误：需求要求按id分组取订阅数最大值，你写的GROUP BY P.Key是按key字段分组，和需求逻辑不符
• 聚合逻辑缺失：常规GROUP BY聚合只能返回分组字段和计算出的聚合值，无法直接关联获取最大值对应的完整行数据，会出现key字段和最大值不匹配的问题

你可以根据自己的需求，从下面两类实现里选择：

方案一：筛选目标记录，可直接插入新表 #

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等） #

用ROW_NUMBER()窗口函数是最简洁易维护的写法：

-- 直接查询符合要求的结果
SELECT id, [key], [nbr of subs]
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY [nbr of subs] DESC) AS row_rank
    FROM P
) ranked
WHERE row_rank = 1;

如果需要把结果插入新表，只需要在SELECT语句前加上对应插入语法即可，比如要插入到提前建好的P_result表：

INSERT INTO P_result (id, [key], [nbr of subs])
SELECT id, [key], [nbr of subs]
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY [nbr of subs] DESC) AS row_rank
    FROM P
) ranked
WHERE row_rank = 1;

不支持窗口函数的旧版数据库（如MySQL 5.x） #

可以用聚合子查询关联原表取数：

SELECT p1.*
FROM P p1
INNER JOIN (
    SELECT id, MAX([nbr of subs]) AS max_sub_cnt
    FROM P
    GROUP BY id
) p2 ON p1.id = p2.id AND p1.[nbr of subs] = p2.max_sub_cnt;

注意：如果同一个id下存在多条订阅数相等且都是最大值的记录，上面两种写法会返回所有符合条件的记录。如果每个id只需要保留一条，可以在排序规则里加第二个排序字段（比如ORDER BY [nbr of subs] DESC, [key]），保证结果唯一。

方案二：直接删除原表内非最大值的冗余记录 #

如果不需要保留原表的多余数据，可以直接执行删除操作：

支持窗口函数的数据库 #

DELETE FROM P
WHERE EXISTS (
    SELECT 1 FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY [nbr of subs] DESC) AS row_rank
        FROM P
    ) ranked
    WHERE ranked.id = P.id AND ranked.[key] = P.[key] AND row_rank > 1
);

MySQL 5.x 版本 #

DELETE p1
FROM P p1
LEFT JOIN (
    SELECT
        id,
        MAX([nbr of subs]) AS max_sub_cnt,
        MIN([key]) AS keep_key -- 同最大值时保留key最小的记录，可按需调整规则
    FROM P
    GROUP BY id
) p2 ON p1.id = p2.id AND p1.[nbr of subs] = p2.max_sub_cnt AND p1.[key] = p2.keep_key
WHERE p2.id IS NULL;

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