MySQL分组查询：获取每月游客量最高的国家

表结构与示例数据 #

user表 #

bookings表 #

常见尝试的SQL（存在的问题） #

很多人会先写出统计每月各国游客数的SQL，但无法直接筛选出每月最高的国家：

SELECT 
    MONTH(b.booking_date) AS month_num,
    u.country,
    COUNT(DISTINCT u.user_id) AS visitor_count
FROM bookings b
JOIN user u ON b.user_id = u.user_id
WHERE YEAR(b.booking_date) = 2022
GROUP BY month_num, u.country
ORDER BY month_num, visitor_count DESC;

这个语句能列出所有月份的各国游客数，但没法直接提取每个月的Top1记录。

解决方案 #

方案1：窗口函数（MySQL 8.0+ 推荐） #

用ROW_NUMBER()窗口函数给每个月内的国家按游客数排序，直接筛选排名第一的记录。如果要保留并列第一的所有国家，把ROW_NUMBER()换成RANK()即可。

WITH monthly_visitor AS (
    SELECT 
        MONTH(b.booking_date) AS month_num,
        u.country,
        COUNT(DISTINCT u.user_id) AS visitor_count,
        -- 每个月内按游客数降序排名
        ROW_NUMBER() OVER (PARTITION BY MONTH(b.booking_date) ORDER BY COUNT(DISTINCT u.user_id) DESC) AS rn
    FROM bookings b
    JOIN user u ON b.user_id = u.user_id
    WHERE YEAR(b.booking_date) = 2022
    GROUP BY month_num, u.country
)
SELECT month_num, country, visitor_count
FROM monthly_visitor
WHERE rn = 1
ORDER BY month_num;

方案2：子查询关联（兼容MySQL 5.x） #

如果你的MySQL版本不支持窗口函数，用子查询先统计每月最大游客数，再关联回各国统计结果：

SELECT 
    t.month_num,
    t.country,
    t.visitor_count
FROM (
    -- 统计每月各国游客数
    SELECT 
        MONTH(b.booking_date) AS month_num,
        u.country,
        COUNT(DISTINCT u.user_id) AS visitor_count
    FROM bookings b
    JOIN user u ON b.user_id = u.user_id
    WHERE YEAR(b.booking_date) = 2022
    GROUP BY month_num, u.country
) t
JOIN (
    -- 统计每月的最大游客数
    SELECT 
        month_num,
        MAX(visitor_count) AS max_visitor
    FROM (
        SELECT 
            MONTH(b.booking_date) AS month_num,
            COUNT(DISTINCT u.user_id) AS visitor_count
        FROM bookings b
        JOIN user u ON b.user_id = u.user_id
        WHERE YEAR(b.booking_date) = 2022
        GROUP BY month_num, u.country
    ) temp
    GROUP BY month_num
) m ON t.month_num = m.month_num AND t.visitor_count = m.max_visitor
ORDER BY t.month_num;

输出结果示例 #

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