如何统计SQL查询结果中specialty列的最频繁值？

解决方案 #

要统计specialty列出现最频繁的值，可以通过分组统计加排序的方式实现，以下是几种常见场景的SQL写法：

1. 仅获取出现次数最多的单个值（若有多个并列最高，仅返回第一个）

SELECT specialty, COUNT(*) AS occurrence_count
FROM (
    select ap.doctorsnum,doc.specialty
    from appointments as ap
    join doctor as doc
    on ap.doctorsnum = doc.doctorsnum
) AS temp
GROUP BY specialty
ORDER BY occurrence_count DESC
LIMIT 1;

2. 获取所有出现次数最多的值（处理并列最高的情况）

如果存在多个specialty出现次数相同且均为最高值，上面的写法会漏掉其他并列项，这时可以先统计所有专科的频次，再筛选出频次等于最高值的结果：

WITH specialty_counts AS (
    SELECT specialty, COUNT(*) AS occurrence_count
    FROM (
        select ap.doctorsnum,doc.specialty
        from appointments as ap
        join doctor as doc
        on ap.doctorsnum = doc.doctorsnum
    ) AS temp
    GROUP BY specialty
)
SELECT specialty, occurrence_count
FROM specialty_counts
WHERE occurrence_count = (SELECT MAX(occurrence_count) FROM specialty_counts);

简化写法（直接基于原表关联统计）

其实不用把原查询嵌套成子查询，直接对关联后的结果做统计即可，简化后的SQL（以第一种场景为例）：

SELECT doc.specialty, COUNT(*) AS occurrence_count
FROM appointments AS ap
JOIN doctor AS doc ON ap.doctorsnum = doc.doctorsnum
GROUP BY doc.specialty
ORDER BY occurrence_count DESC
LIMIT 1;

关键逻辑说明 #

• GROUP BY specialty：按专科名称分组，把相同专科的记录归为一组
• COUNT(*)：计算每组的记录数，也就是对应专科的出现频次
• ORDER BY occurrence_count DESC：按频次从高到低排序，让出现次数最多的专科排在最前面
• LIMIT 1：只取排序后的第一条结果，适用于只需要一个最高频值的场景
• 第二种写法用CTE（公共表表达式）先缓存所有专科的频次数据，再筛选出频次等于最大值的所有结果，能完整返回所有并列最高的专科

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