MySQL 求众数、稀数

数据表

假设有 student 表，数据如下：

+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 刘备     |    90 |
|  2 | 曹操    |    90 |
|  3 | 孙权    |    90 |
|  4 | 刘表     |    50 |
|  5 | 刘封     |    30 |
|  6 | 张飞    |  NULL |
+----+------+-------+

建表语句如下：

+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                               |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

问题 1 ：求出现次数最多的分数、出现次数最少的分数

  （1）select score from student where score is not null group by score order by count(*) desc limit 1; -- 出现次数最多的分数

+-------+
| score |
+-------+
|    90 |
+-------+

  （2）select score from student where score is not null group by score order by count(*) asc limit 1; -- 出现次数最少的分数

+-------+
| score |
+-------+
|    50 |
+-------+

问题 2：求出现次数最多的姓、出现次数最少的姓

  （1）select left(name,1) from student group by left(name,1) order by count(*) desc limit 1; -- 出现次数最多的姓

+--------------+
| left(name,1) |
+--------------+
| 刘             |
+--------------+

（2）select left(name,1) from student group by left(name,1) order by count(*) asc limit 1; -- 出现次数最少的姓

+--------------+
| left(name,1) |
+--------------+
| 曹             |
+--------------+

问题 3：求出现次数最多的分数区间（ 划分为两个区间[0, 60)、[60, 100] ，还可以划分的更细，这里只划分为 2 个）、出现次数最少的分数区间

  (1) select if(score<60,'[0, 60)','[60, 100]') from student where score is not null group by if(score<60,'[0, 60)','[60, 100]') order by count(*) desc limit 1; -- 出现次数最多的分数区间

+------------------------------------+
| if(score<60,'[0, 60)','[60, 100]') |
+------------------------------------+
| [60, 100]                          |
+------------------------------------+

(2) select if(score<60,'[0, 60)','[60, 100]') from student where score is not null group by if(score<60,'[0, 60)','[60, 100]') order by count(*) asc limit 1;-- 出现次数最少的分数区间

+------------------------------------+
| if(score<60,'[0, 60)','[60, 100]') |
+------------------------------------+
| [0, 60)                            |
+------------------------------------+

缺陷及改正

其实、众数或稀疏数可能有多个，比如问题 2 中的出现次数最少的姓名不知一个，包括 孙、张、曹。

考虑到这种情况的 sql 应该这么写：

select left(name, 1) from student group by left(name, 1) having count(*) = (select count(*) from student group by left(name,1) order by count(*) asc limit 1);

执行 sql，得到结果集

+---------------+
| left(name, 1) |
+---------------+
| 孙              |
| 张              |
| 曹              |
+---------------+

其他问题中的情况类似。