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) |
+---------------+
| 孙 |
| 张 |
| 曹 |
+---------------+
其他问题中的情况类似。