来看这样一个表：
- 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>
好了， 不多说， 总之， group就是聚合分组。
|
