MySQL 分组计数
练习 group by 和 count 语句
情景一
假设有 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：求各个姓氏出现的次数
提示：group by 后不只限于跟一个列名，还可以可以跟一个 ''记录的函数''
解：select left(name, 1), count(*) from student group by left(name, 1);
得：
+---------------+----------+
| left(name, 1) | count(*) |
+---------------+----------+
| 关 | 2 |
| 刘 | 4 |
| 张 | 1 |
| 曹 | 3 |
+---------------+----------+
问题 2：求各个姓氏出现的次数，并按出现次数由高到低排序
解：select left(name, 1), count(*) from student group by left(name, 1) order by count(*) desc;
得：
+---------------+----------+
| left(name, 1) | count(*) |
+---------------+----------+
| 刘 | 4 |
| 曹 | 3 |
| 关 | 2 |
| 张 | 1 |
+---------------+----------+
情景二
假设有 student 表，如下：
+----+--------+---------------------+
| id | name | birthday |
+----+--------+---------------------+
| 1 | Alice1 | 1997-01-15 16:21:19 |
| 2 | Alice2 | 1998-03-18 17:19:54 |
| 3 | Alice3 | 1996-06-07 03:15:30 |
| 4 | Alice4 | 1998-03-08 10:10:42 |
| 5 | Alice5 | 1997-05-04 07:12:37 |
| 6 | Alice6 | 1998-01-28 09:09:17 |
+----+--------+---------------------+
问题 ：按年进行分组计数
解：select year(birthday), count(*) from student group year(birthday);
得：
+----------------+----------+
| year(birthday) | count(*) |
+----------------+----------+
| 1996 | 1 |
| 1997 | 2 |
| 1998 | 3 |
+----------------+----------+
group by 后可以跟多列，请看情景三...
情景三
假设有 student 表，
表结构：
+----------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('男','女') | NO | | NULL | |
| class_id | int(11) | NO | | NULL | |
+----------+-----------------+------+-----+---------+----------------+
数据如下：
+----+---------+-----+----------+
| id | name | sex | class_id |
+----+---------+-----+----------+
| 1 | 张三1 | 男 | 1 |
| 2 | 张三2 | 男 | 2 |
| 3 | 张三3 | 男 | 3 |
| 4 | 李四1 | 男 | 1 |
| 5 | 王五1 | 男 | 1 |
| 6 | 林黛玉1 | 女 | 1 |
| 7 | 王熙凤1 | 女 | 1 |
| 8 | 李四2 | 男 | 2 |
| 9 | 王五2 | 男 | 2 |
| 10 | 刘备2 | 男 | 2 |
| 11 | 关羽2 | 男 | 2 |
+----+---------+-----+----------+
问题 ：统计每个班级的男、女生人数
提示：group by 可以跟多个''记录的函数''
解 1：select class_id, sex, count(*) from student group by class_id, sex;
得：
+----------+-----+----------+
| class_id | sex | count(*) |
+----------+-----+----------+
| 1 | 男 | 3 |
| 1 | 女 | 2 |
| 2 | 男 | 5 |
| 3 | 男 | 1 |
+----------+-----+----------+
带排序效果：select class_id, sex, count(*) from student group by class_id, sex order by class_id asc, sex asc;
得：
+----------+-----+----------+
| class_id | sex | count(*) |
+----------+-----+----------+
| 1 | 男 | 3 |
| 1 | 女 | 2 |
| 2 | 男 | 5 |
| 3 | 男 | 1 |
+----------+-----+----------+
也可以将class_id 和 sex 合并为一列显示：
解 2：select concat(class_id, '班', sex, '生人数'), count(*) from student group by class_id, sex;
得
+---------------------------------------+----------+
| concat(class_id, '班', sex, '生人数') | count(*) |
+---------------------------------------+----------+
| 1班男生人数 | 3 |
| 1班女生人数 | 2 |
| 2班男生人数 | 5 |
| 3班男生人数 | 1 |
+---------------------------------------+----------+
带排序效果：select concat(class_id, '班', sex, '生人数'), count(*) from student group by class_id, sex order by class_id asc, sex asc;
得
+---------------------------------------+----------+
| concat(class_id, '班', sex, '生人数') | count(*) |
+---------------------------------------+----------+
| 1班男生人数 | 3 |
| 1班女生人数 | 2 |
| 2班男生人数 | 5 |
| 3班男生人数 | 1 |
+---------------------------------------+----------+
