SQL查询：手术量Top10医生及对应最高频手术类型统计

你的初始SQL思路方向没错，但存在两个核心问题导致结果不符合预期：

• 子查询虽然统计了每个医生对应各手术类型的完成数量，但和主表关联后没有做最大值筛选，按医生分组时会随机返回该医生名下任意一个手术类型，根本不是频次最高的目标值。
• 子查询中统计的单类型手术量没有设置别名，在开启ONLY_FULL_GROUP_BY模式的数据库环境下会直接抛出语法错误。

下面给两种适配不同数据库版本的写法，逻辑清晰无冗余：

写法1：窗口函数实现（推荐，适配MySQL8.0+、PostgreSQL等所有支持窗口函数的数据库，性能最优） #

逻辑拆分为三个简单步骤，避免多层嵌套的混乱：

1. 先统计所有医生的总手术量，按倒序取前10名
2. 统计每个医生名下各手术类型的完成频次，用窗口函数给同一名医生下的手术类型按频次倒序打排名
3. 关联两份结果，只取每个医生排名第1的手术类型，就是他完成频次最高的手术

WITH doctor_total AS (
    SELECT 
        doctor,
        COUNT(*) AS num_procedures
    FROM schedule
    GROUP BY doctor
    ORDER BY num_procedures DESC
    LIMIT 10
),
doctor_type_rank AS (
    SELECT
        doctor,
        type,
        ROW_NUMBER() OVER (
            PARTITION BY doctor 
            ORDER BY COUNT(*) DESC
        ) AS type_rank
    FROM schedule
    GROUP BY doctor, type
)
SELECT
    t.doctor,
    t.num_procedures,
    r.type AS most_frequent_procedure
FROM doctor_total t
LEFT JOIN doctor_type_rank r
ON t.doctor = r.doctor AND r.type_rank = 1;

补充说明：如果同一名医生有多个手术类型的完成频次完全并列第一，ROW_NUMBER()会随机返回其中一个；如果需要把所有并列第一的类型都展示出来，把ROW_NUMBER()替换成RANK()即可。

写法2：旧版本兼容写法（适配MySQL5.x等不支持窗口函数、CTE的环境） #

如果你的数据库版本较旧，可以用关联子查询实现，逻辑直白易维护，数据量不大的场景下性能完全够用：

SELECT
    t.doctor,
    t.num_procedures,
    (
        SELECT type
        FROM schedule
        WHERE doctor = t.doctor
        GROUP BY type
        ORDER BY COUNT(*) DESC
        LIMIT 1
    ) AS most_frequent_procedure
FROM (
    SELECT
        doctor,
        COUNT(*) AS num_procedures
    FROM schedule
    GROUP BY doctor
    ORDER BY num_procedures DESC
    LIMIT 10
) t;

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