MySQL如何查询单表多个字段各自出现频次最高的众数值

针对同时提取数据表多个字段众数的需求，以下提供两种可直接运行的实现方式，均满足「平局时任意返回一个频次最高值」的要求，基于示例表演示：

CREATE TABLE words(`letter1` char(1), `letter2` char(1));
    
INSERT INTO words(`letter1`, `letter2`)VALUES
    ('A', 'A'),    ('B', 'A'),    ('C', 'A'),    ('D', 'A'),    ('D', 'B'),
    ('B', 'B'),    ('D', 'B'),    ('A', 'C'),    ('B', 'D'),    ('D', 'A');

方案1：MySQL 8.0及以上版本（推荐，易扩展） #

利用CTE做行转列，配合窗口函数分组排序取Top1，字段多的时候不需要重复写大量子查询：

WITH field_unpivot AS (
    -- 将所有需要统计的字段拆分为「字段名+字段值」的统一结构
    SELECT 'letter1' AS field_name, letter1 AS field_value FROM words
    UNION ALL
    SELECT 'letter2' AS field_name, letter2 AS field_value FROM words
),
freq_rank AS (
    -- 按字段分组，统计每个值的出现频次并按频次降序排名
    SELECT
        field_name,
        field_value,
        COUNT(*) AS appear_times,
        ROW_NUMBER() OVER (PARTITION BY field_name ORDER BY COUNT(*) DESC) AS rank_num
    FROM field_unpivot
    GROUP BY field_name, field_value
)
-- 取每个字段排名第1的值即为众数
SELECT field_name, field_value AS mode_value, appear_times
FROM freq_rank
WHERE rank_num = 1;

运行返回结果：

• letter1的众数为D，共出现4次
• letter2的众数为A，共出现5次

如果需要平局时返回所有频次并列最高的值，把ROW_NUMBER()替换为RANK()即可。

方案2：兼容MySQL 5.x 低版本 #

针对不支持窗口函数的老版本，可以直接用子查询+LIMIT 1分别取每个字段的众数，再合并结果，逻辑直观：

-- 长表格式返回结果
SELECT 'letter1' AS field_name, 
    (SELECT letter1 FROM words GROUP BY letter1 ORDER BY COUNT(*) DESC LIMIT 1) AS mode_value
UNION ALL
SELECT 'letter2' AS field_name, 
    (SELECT letter2 FROM words GROUP BY letter2 ORDER BY COUNT(*) DESC LIMIT 1) AS mode_value;

如果需要直接返回一行两列的宽表结果，不需要字段名列，可以简化为：

SELECT
    (SELECT letter1 FROM words GROUP BY letter1 ORDER BY COUNT(*) DESC LIMIT 1) AS letter1_mode,
    (SELECT letter2 FROM words GROUP BY letter2 ORDER BY COUNT(*) DESC LIMIT 1) AS letter2_mode;

两种写法都能正确返回对应字段的众数，字段新增时只需要按相同格式追加对应子查询即可。

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