SQL 分组/不分组求众数

原始表table_name 如下：

一、不分组求score的众数

select name,subject,score from table_name group by score
having count(*) >= (
select max(cn) from (select *,count(*) cn from table_name group by score) a);

结果如下：(有2个众数)

二、分组求score的众数

根据subject分组

select name,subject,score from(
select *,max(cn) from(
select *,count(*) cn from table_name group by subject,score) a
where cn >= 2 #过滤掉没有众数的数据
group by subject) b;

结果如下：