SQL基于列值频率查询行：如何获取每位厨师的最高频菜系

实现逻辑 #

按厨师维度拆分统计维度，先计算每个厨师制作不同菜系的餐食次数，筛选出每个厨师名下出现次数最高的菜系值，即可匹配目标输出结果。

方案1：适配支持窗口函数的现代数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等） #

这是最简洁的实现方式，通过窗口函数对每个厨师的菜系频次排序，直接取排名第一的记录：

-- 假设存储餐食记录的原表名为 chef_meal_records
WITH cuisine_frequency AS (
    SELECT
        chef_name,
        chef_cuisine,
        -- 按厨师分区，将各菜系按出现次数从高到低排序
        ROW_NUMBER() OVER (
            PARTITION BY chef_name
            ORDER BY COUNT(*) DESC
        ) AS row_rank
    FROM chef_meal_records
    GROUP BY chef_name, chef_cuisine
)
SELECT chef_name, chef_cuisine
FROM cuisine_frequency
WHERE row_rank = 1;

对应计算结果完全匹配预期：

• 厨师mario制作意大利餐2次、美式餐1次，最终取最高频的italian
• 厨师jack制作希腊餐2次、美式餐1次，最终取最高频的greek

注意：如果存在同一厨师两个菜系制作次数完全持平的场景，ROW_NUMBER()会随机选取其中一个返回；若需要保留所有频次并列最高的结果，可将ROW_NUMBER()替换为RANK()。

方案2：适配不支持窗口函数的老旧数据库（如MySQL 5.x） #

低版本数据库不支持CTE和窗口语法时，可通过两层子查询关联实现同等效果：

SELECT freq.chef_name, freq.chef_cuisine
FROM (
    -- 统计每个厨师对应各菜系的制作次数
    SELECT
        chef_name,
        chef_cuisine,
        COUNT(*) AS cuisine_count
    FROM chef_meal_records
    GROUP BY chef_name, chef_cuisine
) freq
INNER JOIN (
    -- 提取每个厨师的最高制作次数阈值
    SELECT
        chef_name,
        MAX(cuisine_count) AS max_cuisine_count
    FROM (
        SELECT chef_name, chef_cuisine, COUNT(*) AS cuisine_count
        FROM chef_meal_records
        GROUP BY chef_name, chef_cuisine
    ) chef_sub_group
    GROUP BY chef_name
) max_freq
ON freq.chef_name = max_freq.chef_name
AND freq.cuisine_count = max_freq.max_cuisine_count;

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