mysql 组内计数_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 |

+---------------------------------------+----------+