Oracle SQL如何通过分组查询获取每个ID对应最大访问次数的国家

我们需要对数据库存储的业务数据做分组频次统计，查询每个ID对应的访问次数（NumberOfVisits）最高的国家（Country），最终仅返回ID、对应国家两个字段。

示例数据 #

期望输出 #

实现方案 #

通用方案（支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、SQL Server、Oracle等） #

使用窗口函数按ID分组排序后取排名第一的记录即可：

SELECT ID, Country
FROM (
    SELECT 
        ID,
        Country,
        RANK() OVER(PARTITION BY ID ORDER BY NumberOfVisits DESC) AS visit_rank
    FROM 业务表名
) AS rank_result
WHERE visit_rank = 1;

补充说明：如果存在同一个ID下多个国家访问次数并列最高的情况，以上语句会返回所有并列最高的国家记录；如果仅需要任意取一个，把RANK()替换为ROW_NUMBER()即可。

兼容老版本MySQL（不支持窗口函数）方案 #

先分组统计每个ID的最高访问次数，再关联原表匹配对应的国家：

SELECT t1.ID, t1.Country
FROM 业务表名 t1
INNER JOIN (
    SELECT ID, MAX(NumberOfVisits) AS max_visit_count
    FROM 业务表名
    GROUP BY ID
) t2 ON t1.ID = t2.ID AND t1.NumberOfVisits = t2.max_visit_count;

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