(1) group by 的含义: 将查询结果按照 1 个或多个字段进行分组,字段值相同的为一组
(2) group by 可用于单个字段分组,也可用于多个字段分组
- select * from employee;
- +------+------+--------+------+------+-------------+
- | num | d_id | name | age | sex | homeaddr |
- +------+------+--------+------+------+-------------+
- | 1 | 1001 | 张三 | 26 | 男 | beijinghdq |
- | 2 | 1002 | 李四 | 24 | 女 | beijingcpq |
- | 3 | 1003 | 王五 | 25 | 男 | changshaylq |
- | 4 | 1004 | Aric | 15 | 男 | England |
- +------+------+--------+------+------+-------------+
- select * from employee group by d_id,sex;
- select * from employee group by sex;
- +------+------+--------+------+------+------------+
- | num | d_id | name | age | sex | homeaddr |
- +------+------+--------+------+------+------------+
- | 2 | 1002 | 李四 | 24 | 女 | beijingcpq |
- | 1 | 1001 | 张三 | 26 | 男 | beijinghdq |
- +------+------+--------+------+------+------------+
- 根据sex字段来分组,sex字段的全部值只有两个('男'和'女'),所以分为了两组
- 当group by单独使用时,只显示出每组的第一条记录
- 所以group by单独使用时的实际意义不大
(1) group_concat(字段名) 可以作为一个输出字段来使用,
(2) 表示分组之后,根据分组结果,使用 group_concat() 来放置每一组的某字段的值的集合
- select sex from employee group by sex;
- +------+
- | sex |
- +------+
- | 女 |
- | 男 |
- +------+
- select sex,group_concat(name) from employee group by sex;
- +------+--------------------+
- | sex | group_concat(name) |
- +------+--------------------+
- | 女 | 李四 |
- | 男 | 张三,王五,Aric |
- +------+--------------------+
- select sex,group_concat(d_id) from employee group by sex;
- +------+--------------------+
- | sex | group_concat(d_id) |
- +------+--------------------+
- | 女 | 1002 |
- | 男 | 1001,1003,1004 |
- +------+--------------------+
(1) 通过 group_concat() 的启发,我们既然可以统计出每个分组的某字段的值的集合,那么我们也可以通过集合函数来对这个" 值的集合 " 做一些操作
- select sex,group_concat(age) from employee group by sex;
- +------+-------------------+
- | sex | group_concat(age) |
- +------+-------------------+
- | 女 | 24 |
- | 男 | 26,25,15 |
- +------+-------------------+
- 分别统计性别为男/女的人年龄平均值
- select sex,avg(age) from employee group by sex;
- +------+----------+
- | sex | avg(age) |
- +------+----------+
- | 女 | 24.0000 |
- | 男 | 22.0000 |
- +------+----------+
- 分别统计性别为男/女的人的个数
- select sex,count(sex) from employee group by sex;
- +------+------------+
- | sex | count(sex) |
- +------+------------+
- | 女 | 1 |
- | 男 | 3 |
- +------+------------+
(1) having 条件表达式:用来分组查询后指定一些条件来输出查询结果
(2) having 作用和 where 一样,但 having 只能用于 group by
- select sex,count(sex) from employee group by sex having count(sex)>2;
- +------+------------+
- | sex | count(sex) |
- +------+------------+
- | 男 | 3 |
- +------+------------+
(1) with rollup 的作用是:在最后新增一行,来记录当前列里所有记录的总和
- select sex,count(age) from employee group by sex with rollup;
- +------+------------+
- | sex | count(age) |
- +------+------------+
- | 女 | 1 |
- | 男 | 3 |
- | NULL | 4 |
- +------+------------+
- select sex,group_concat(age) from employee group by sex with rollup;
- +------+-------------------+
- | sex | group_concat(age) |
- +------+-------------------+
- | 女 | 24 |
- | 男 | 26,25,15 |
- | NULL | 24,26,25,15 |
- +------+-------------------+
[Mysql 查询语句]——分组查询 group by
来源: http://www.bubuko.com/infodetail-2251087.html