首页 > 代码库 > group by的使用

group by的使用

group  by的使用

环境:win8.1  mysql5.7

“group  by”就是根据“by”指定的规则对数据进行分组,所谓的分组就是将一个“数据集”划分成若干个“小区域”,然后针对若干个“小区域”进行数据处理。

原始表:

技术分享

简单的group  by

示例1

select  *  from t_student  group  by  stu_subject;  

结果

技术分享

可以看出,单独地使用group  by的实际意义并不大,它只能显示出没组记录的第一条记录

group  by和order  by

示例2

select  * from t_student  group  by  stu_subject  order  by  stu_id;

结果

技术分享

group  by + group_concat()

group_concat(字段名)可作为一个输出字段使用,表示分组后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合

技术分享

group  by + 集合函数

比如假设我们要计算出表中每人的平均分

技术分享

假设我们要计算出表中单科每人超过80分的数量

技术分享

group + having

having:用来分组查询后指定一些条件来输出查询结果,having作用和where类似,但是having只能用在group  by场合,并且必须位于group  by之后order  by之前

比如我们要查找表中平均分超过82分的人:

技术分享

顺便也对比一下having和where的区别

举个例子说明:

SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary)>10;

SELECT user, MAX(salary) FROM users GROUP BY user WHERE MAX(salary)>10;

第一个sql语句可以执行,但是第二个会报错

二者作用的对象不同,where子句作用于表和视图,having作用于组。

where在分组和聚集计算之前选取输入行(因而可以控制哪些行进入聚集计算),而having用于过滤group by的聚合,在分组和聚集之后选取分组的行。因为用聚集函数判断那些行输入给聚集运算是没有意义的,所以where不能包含聚集函数,不能使用分组项目

HAVING子句可以引用总计函数,而WHERE子句不能引用

where就不能用在sum之类的函数上,比如这样的错误,模仿前面吧having换成where

技术分享

不要将HAVING用于应被用于WHERE子句的条目,尽管不出错,但是mysql不推荐

stackoverflow上的一个例子:

https://stackoverflow.com/questions/2905292/where-vs-having

首先创建一个数据表:

CREATE TABLE `wherehaving` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `value` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

数据增加:

INSERT INTO `wherehaving`(`id`, `value`) VALUES (1, 1),(2, 2),(3, 3),(4, 4),(5, 5),(6, 6),(7, 7),(8, 8),(9, 9),(10, 10);

技术分享

我们通过下面两句来对比一下:

SELECT `value` v FROM `table` WHERE `value`>5; -- Get 5 rows
SELECT `value` v FROM `table` HAVING `value`>5; -- Get 5 rows
上面这两句都是可以运行的
SELECT `value` v FROM `table` WHERE `v`>5;

Error #1054 - Unknown column ‘v‘ in ‘where clause‘

SELECT `value` v FROM `table` HAVING `v`>5; -- Get 5 rows

技术分享

where子句要求条件是表中的列,单having子句可以使用列或者别名。这是因为where子句在选择之前过滤数据,但having子句在选择之后过滤数据

详细地看一下

技术分享

技术分享

前面提到了having用于过滤group  by的聚合,也介绍了一些聚合函数,现在我们来看一下group by上常用的一些聚合函数

group和聚合函数

函数  作用   支持性
sum(列名) 求和  
max(列名) 最大值  
min(列名) 最小值  
avg(列名) 平均值  
first(列名) 第一条记录 仅Access支持
last(列名) 最后一条记录 仅Access支持
count(列名) 统计记录数 注意和count(*)的区别

group by + with rollup

在最后新增一行,来记录当前列里所有记录的总和

技术分享

技术分享

 

Technorati Tags: mysql

group by的使用