mysql中group by的使用示例------聚合分组- 数据库

来看这样一个表：

mysql> show create table tb_student;
+------------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                        |
+------------+-------------------------------------------------------------------------------------------------------------------------------------+
| tb_student | CREATE TABLE `tb_student` (
  `name` varchar(64) NOT NULL,
  `score` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> desc tb_student;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | varchar(64)      | NO   |     | NULL    |       |
| score | int(10) unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

其中的记录是：

mysql> select * from tb_student;
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan |   100 |
| zhangsan |    90 |
| lisi     |    80 |
| lisi     |    70 |
| lisi     |    30 |
| lisi     |     0 |
| lisi     |     0 |
+----------+-------+
7 rows in set (0.00 sec)

现在，我们要对这个数据进行统计，以name为维度，看看每个人考试的次数，此时需要用group by对人进行聚合组：

mysql> select name, count(*) cnt from tb_student group by name;
+----------+-----+
| name     | cnt |
+----------+-----+
| lisi     |   5 |
| zhangsan |   2 |
+----------+-----+
2 rows in set (0.00 sec)

可见lisi考了5次， zhangsan考了2次。

（小细节： cnt之前可以加上as, 不加也没关系）

还是以name为维度，看每个人考试的总分：

mysql> select name, sum(score) sum from tb_student group by name;
+----------+------+
| name     | sum  |
+----------+------+
| lisi     |  180 |
| zhangsan |  190 |
+----------+------+
2 rows in set (0.00 sec)

mysql>

可见， lisi总分是180分， zhangsan总分是190分。

上面两种情况，如果要进行排序，加上order by就能轻易搞定。

另外，有时候需要针对某项进行去重，则可以这么搞起：

mysql> select name, count(distinct score) cnt from tb_student group by name;
+----------+-----+
| name     | cnt |
+----------+-----+
| lisi     |   4 |
| zhangsan |   2 |
+----------+-----+
2 rows in set (0.00 sec)

mysql>