“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
在最后新增一行,来记录当前列里所有记录的总和