分组查询——group by
# 分组查询——group by
group by 分组
聚合函数
依据字段
desc逆序排列（默认排列asc）
select <聚合函数>, <依据字段> from <TableName> group by <依据字段>;
1
# Example
+----+--------+------+--------+---------+
| id | name | age | gender | address |
+----+--------+------+--------+---------+
| 1 | 张伟 | 25 | 男 | 上海 |
| 2 | 李星 | 30 | 男 | 武汉 |
| 3 | 陈晓 | 23 | 女 | 北京 |
| 4 | 姚蓝 | 34 | 男 | 上海 |
| 5 | 朱隶 | 42 | 男 | 北京 |
| 6 | 张小雨 | 24 | 女 | 上海 |
| 7 | 丁雪雪 | 21 | 女 | 北京 |
| 8 | 万娇娇 | 18 | 女 | 武汉 |
| 9 | 余苗苗 | 19 | 女 | 上海 |
+----+--------+------+--------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 查询表中男女各自人数
select count(gender) as '人数', gender as '性别' from manager group by gender;
1
+------+------+
| 人数 | 性别 |
+------+------+
| 4 | 男 |
| 5 | 女 |
+------+------+
1
2
3
4
5
6
2
3
4
5
6
# 逆序排列（desc）
select count(gender) as '人数', gender as '性别' from manager group by gender desc;
1
+------+------+
| 人数 | 性别 |
+------+------+
| 5 | 女 |
| 4 | 男 |
+------+------+
1
2
3
4
5
6
2
3
4
5
6
# 查询男女各自平均年龄
select avg(age) as '平均年龄', gender as '性别' from manager group by gender;
1
+----------+------+
| 平均年龄 | 性别 |
+----------+------+
| 32.7500 | 男 |
| 21.0000 | 女 |
+----------+------+
1
2
3
4
5
6
2
3
4
5
6
# 查询各地区人数
select count(address) as '人数', address as '地区' from manager group by address;
1
+------+------+
| 人数 | 地区 |
+------+------+
| 3 | 北京 |
| 4 | 上海 |
| 2 | 武汉 |
+------+------+
1
2
3
4
5
6
7
2
3
4
5
6
7
# group_concat() 函数
select group_concat(name), gender from manager group by gender;
1
+----------------------------------+--------+
| group_concat(name) | gender |
+----------------------------------+--------+
| 张伟,李星,姚蓝,朱隶 | 男 |
| 陈晓,张小雨,丁雪雪,万娇娇,余苗苗 | 女 |
+----------------------------------+--------+
1
2
3
4
5
6
2
3
4
5
6
编辑 (opens new window)
上次更新: 2022/09/26, 16:55:15