如何用MySQL按Route分组，返回每组最常见的Incident类型？

源数据表格 #

期望结果 #

问题分析 #

你之前尝试的两个查询都无法直接得到目标结果：

• 第一个查询仅返回所有原始记录，未做任何统计筛选；
• 第二个查询虽然统计了每个Route+Incident组合的出现次数，但未筛选出每个Route下次数最多的Incident。

方法一：使用窗口函数（MySQL 8.0及以上版本） #

利用窗口函数给每个Route下的Incident按出现次数排名，再筛选排名第一的记录：

WITH incident_counts AS (
    SELECT 
        Route,
        Incident,
        COUNT(*) AS incident_count
    FROM bus_delay
    GROUP BY Route, Incident
),
ranked_incidents AS (
    SELECT 
        Route,
        Incident,
        -- 按Route分组，按出现次数倒序排名
        ROW_NUMBER() OVER (PARTITION BY Route ORDER BY incident_count DESC) AS rn
    FROM incident_counts
)
SELECT Route, Incident
FROM ranked_incidents
WHERE rn = 1;

• 若存在多个Incident出现次数相同的情况，ROW_NUMBER()只会返回其中一条；如果需要返回所有并列第一的记录，可替换为RANK()。

方法二：适用于MySQL 5.x版本（无窗口函数支持） #

通过子查询统计次数，再自连接筛选出每个Route下次数最多的Incident：

SELECT 
    b1.Route,
    b1.Incident
FROM (
    SELECT Route, Incident, COUNT(*) AS cnt
    FROM bus_delay
    GROUP BY Route, Incident
) b1
LEFT JOIN (
    SELECT Route, Incident, COUNT(*) AS cnt
    FROM bus_delay
    GROUP BY Route, Incident
) b2 ON b1.Route = b2.Route AND b1.cnt < b2.cnt
WHERE b2.Route IS NULL;

• 这个方法会返回所有出现次数并列第一的Incident记录。

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