MySQL 求低频数、中频数、高频数
数据表
练习使用 group by having... 语句
假设有 student 表,如下:
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 刘备 | 23 |
| 2 | 关羽 | 22 |
| 3 | 张飞 | 21 |
| 4 | 刘表 | 43 |
| 5 | 刘璋 | 43 |
| 6 | 刘蝉 | 3 |
| 7 | 曹操 | 33 |
| 8 | 曹植 | 13 |
| 9 | 曹丕 | 15 |
| 10 | 关平 | 17 |
+----+------+-----+
问题 1:求出现次数小于 3 次的姓(低频姓)
解:select left(name, 1) from student group by left(name, 1) having count(*)<3;
得到结果集:
+---------------+
| left(name, 1) |
+---------------+
| 关 |
| 张 |
+---------------+
问题 2:求出现次数大于 3 次的姓(高频姓)
解:select left(name, 1) from student group by left(name, 1) having count(*)>3;
得到结果集:
+---------------+
| left(name, 1) |
+---------------+
| 刘 |
+---------------+
问题 3:求出现次数大于 1 次 小于 4 次的姓(中频姓)
解:select left(name, 1) from student group by left(name, 1) having count(*)>1 and count(*)<4;
得到结果集:
+---------------+
| left(name, 1) |
+---------------+
| 关 |
| 曹 |
+---------------+