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