1. group by 的解释:
对哪个字段 ( 哪几个字段)进行分组。
2. group by 的用法:
select 字段 from 表 where 条件 group by 字段1(,字段2,字段3)
select 字段 from 表 group by 字段1(,字段2,字段3)having 过滤条件
ps:where 是先过滤,再分组;having 是分组后再过滤
3. group by 的案例:
- 创建student表:
create table student(
id, int
name,varchar(20)
grade,varchar(20)
salary,varchar(20)
);
- 向student表中插入数据:
insert into student values(1,'zhang','A',1500)
insert into student values(2,'wang','B',3000)
insert into student values(3,'liu','A',1500)
insert into student values(4,'sun','A',3500)
insert into student values(5,'wu','C',2000)
insert into student values(6,'xu','D',2500)
- student 表的结果:
id | name | grade | salary |
1 | zhang | A | 1500 |
2 | wang | B | 3000 |
1 | zhang | A | 1500 |
4 | liu | A | 3500 |
5 | wu | C | 2000 |
6 | xu | D | 2500 |
- 单个字段分组
- SELECT grade FROM student
grade |
A |
B |
A |
A |
C |
D |
-
- SELECT grade FROM student GROUP BY grade
grade |
A |
B |
C |
D |
- 多个字段分组
- SELECT name,SUM(salary) FROM student GROUP BY name,grade
- 按照名字和成绩划分,查看相同名字下的工资总和sum()是聚合函数
- PS: 这里把名字和成绩看成一个整体,只要是name相同,grade不同,就是两条记录
- SELECT name,SUM(salary) FROM student GROUP BY name,grade
name | sum(salary) |
zhang | 3000 |
wang | 3000 |
liu | 3500 |
wu | 2000 |
xu | 2500 |
- Group By中Select指定的字段限制
- select后的字段:
- 要么就要包含在Group By语句的后面,作为分组的依据;
- 要么就要被包含在聚合函数中。
- 错误:
- SELECT name, salary FROM student GROUP BY name
- select 后的字段 salary 不在 group by 后面,所以salary无法显示全部值。
- 正确1:
- SELECT name, salary FROM student GROUP BY name , salary
- select 后的字段 name,salary 都包含在group by 后面,两个字段分组。
- 正确 2:
- SELECT name,MAX(salary) FROM student GROUP BY name
- select 后的字段 salary 虽然不在 group by 后面,但是在聚合函数MAX(salary)里面,所以只会有一个值会正确。
- select后的字段: