求各SQL分组内最大值出现次数的最简SQL查询方案

AI 小北

2026-8-17

解决方案

表结构

ID	POSITION	EXPERIENCE	SALARY
1	top	90	1500
2	bottom	100	1500
3	top	90	750
4	left	90	1000
5	right	100	1300
6	top	90	1500
7	left	80	2000
8	top	80	1000
9	bottom	100	2000
10	left	100	2000

需求

针对上述service表，编写最简SQL查询，按POSITION分组，统计每个分组内薪资最大值的出现次数，输出字段为POSITION和NO_OF_MAXIMUMS（最大值出现次数）。

最简SQL实现

方法1：关联子查询

SELECT 
    POSITION,
    COUNT(*) AS NO_OF_MAXIMUMS
FROM 
    service
WHERE 
    SALARY = (SELECT MAX(SALARY) FROM service s WHERE s.POSITION = service.POSITION)
GROUP BY 
    POSITION;

方法2：窗口函数（性能更优）

如果数据量较大，窗口函数写法的执行效率更高：

SELECT 
    POSITION,
    COUNT(*) AS NO_OF_MAXIMUMS
FROM (
    SELECT 
        POSITION,
        SALARY,
        MAX(SALARY) OVER (PARTITION BY POSITION) AS max_salary
    FROM service
) t
WHERE SALARY = max_salary
GROUP BY POSITION;