分组查询 – group by
group by 和 having
group by:是按照某个字段或者某些字段进行分组。
having:having是对分组之后的数据进行再次过滤
案列,找出每个工作岗位的最高薪资。
这种就需要分组了,因为找的并不是全部的最高薪资,而且每一个工作岗位的最高薪资。分组函数永远都是在group by 之后进行的
是select max(sal) from emp group by job;
注意:分组函数一般都会和group by联合使用,这也是为什么它会被称为分组函数的原因。并且任何一个分组函数(count 、sum、avg、max、min)都是在group by语句执行结束。
当一条sql语句没有group by的话,那么整张表的数据会自成一组
group by 是在where执行结束之后才会执行,所以分组函数不可以用在where后面
多字段查询
select name,max(sal),job from emp group by job;
注意:以上语句在mysql中,查询结果是有的,但是结果没有意义;在oracle数据库当中会报错,语法错误。oracle的语法规则比mysql语法规则严谨。
记住一个规则:当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段
所以,以上代码不能带name,只能是select max(sal).job from emp group by job;
这是求的每个工作岗位的平均薪资。
多个字段能不能联合起来一块分组?
找出不同type的最高数量(一个字段分组)
mysql> select max(num),type from scan_problem_info_item
-> group by type;
+----------+------+
| max(num) | type |
+----------+------+
| 4556 | 1 |
| 33 | 2 |
| 45 | 3 |
| 22 | 4 |
| 21 | 5 |
| 45 | 8 |
+----------+------+
6 rows in set (0.03 sec)
多个字段分组。
案列:找出一每个部门不同工作岗位的最高薪资。
的select deptno.job,max(sal) from emp group by deptno,job;
having
找出不同type的最高数量,要求数量大于40的数据
mysql> select max(num),type from scan_problem_info_item
-> group by type having max(num)>40;
+----------+------+
| max(num) | type |
+----------+------+
| 4556 | 1 |
| 45 | 3 |
| 45 | 8 |
+----------+------+
3 rows in set (0.04 sec)
这样写的话,效率会有些低。建议用where来过滤,因为where可以直接过滤,提高效率。where比group by先执行。所以使用where效率较高,建议能使用where的尽量使用where
mysql> select max(num),type from scan_problem_info_item
-> where num > 40
-> group by type;
+----------+------+
| max(num) | type |
+----------+------+
| 45 | 3 |
| 45 | 8 |
| 4556 | 1 |
+----------+------+
3 rows in set (0.00 sec)
但是也有使用where搞不定的,比如,找出type的平均薪资大于30的数据。
mysql> select avg(num),type from scan_problem_info_item
-> group by type
-> having
-> avg(num)>30;
+-----------+------+
| avg(num) | type |
+-----------+------+
| 2289.5000 | 1 |
| 32.5000 | 2 |
+-----------+------+
2 rows in set (0.04 sec)
where后面不可以加分组函数。但是上面那个用where,是因为num不是分完组之后算出来的。而平均数是分完组之后算出来的
所以必须用having。
分组函数的执行顺序:
根据条件查询数据
分组
采用having过滤,取得正确的数据
总结
总结一个完整的DQL语句怎么写?
select …… ⑸ from …… ⑴ where …… ⑵ group by …… ⑶ having …… ⑷ order by …… ⑹
关于查询结果集的去重
distinct关键字 — 去除重复记录
mysql> select distinct type_name from scan_problem_info_item;
+--------------------+
| type_name |
+--------------------+
| 基础信息缺失 |
| 抓拍设备异常 |
| 数据质量问题 |
| 视频设备异常 |
| 其他 |
| heosjeo |
| sjeo_djoe |
+--------------------+
7 rows in set (0.03 sec)
注意:distinct只能出现在所有字段的最前面。他是后面所有的字段联合起来去除重复的记录。
统计type问题类型的数量
mysql> select count(distinct type_name) from scan_problem_info_item;
+---------------------------+
| count(distinct type_name) |
+---------------------------+
| 7 |
+---------------------------+
1 row in set (0.02 sec)