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 | +–----±------------------+