分组查询

# 分组查询

书写顺序

select 
  ...
from
  ...
where
  ...
group by
  ...
having (必须和group by联合使用，不可单独使用，对分组后的数据进一步过滤)
  ...
order by
  ...
limit
  ...

1
2
3
4
5
6
7
8
9
10
11
12
13
14

执行顺序

from
  ...
where
  ...
group by
  ...
having (必须和group by联合使用，不可单独使用，对分组后的数据进一步过滤)
  ...
select
  ...
order by
  ...
limit
  ...

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# group by

mysql> select name,address,avg(chinese) from student where gender = '男' group by address;
+------+---------+--------------+
| name | address | avg(chinese) |
+------+---------+--------------+
| 刘邦 | NULL    |           95 |
| 郭靖 | 北京    |           97 |
| 王维 | 天津    |           99 |
+------+---------+--------------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

以上语句在 mysql中可以正常运行，但在 oracle 中会报错，因为没有意义。

在 select语句当中，如果后面有 group by 语句的话，select 语句后面只能跟组合函数以及参加分组的字段，其他一律不能写。

mysql> select avg(chinese) from student where gender = '男' group by address;
+--------------+
| avg(chinese) |
+--------------+
|           95 |
|           97 |
|           99 |
+--------------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

mysql> select address,avg(chinese) from student where gender= '男' group by address;
+---------+--------------+
| address | avg(chinese) |
+---------+--------------+
| NULL    |           95 |
| 北京    |           97 |
| 天津    |           99 |
+---------+--------------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

mysql> select address,avg(chinese) from student where gender= '男' group by address having avg(chinese)>97;
+---------+--------------+
| address | avg(chinese) |
+---------+--------------+
| 天津    |           99 |
+---------+--------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

distinct 只能出现在字段的最前端。如果后面有多个字段，就是多个字段联合起来去重。

编辑

#MYSQL

上次更新: 2025/02/10, 20:20:37

← 函数连接查询→