Alex的博客

本博客的文章均为原创,是本人从事行业多年来所遇见一些小问题的解决心得,希望可以帮助到大家!



group by 分组查询

(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单独使用时的实际意义不大

 

 

#group by + group_concat()

(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 | 
+------+--------------------+

 

 

#group by + 集合函数

(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 | 
+------+------------+

 

 

#group by + having

(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 | 
+------+------------+

 

 

#group by + with rollup

(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 | 
+------+-------------------+

浏览411  评论0  Alex于 2017-8-27 9:10
发言