11-GROUP BY子句
8.5 GROUP BY子句
本节视频教学录像:6分钟MySQL 使用GROUP BY将查询结果根据某一列或多列的值进行分组,值相等的为一组,分组的目的一般与统计有关。分组之前,统计的是整个查询结果,分组后统计的是每一个组,即每个组上都会得到一个函数结果。
1.单字段分组
【范例8-27】
统计不同系别的学生人数。
MySQL> select sdept,count(*) count_sdept -> from student -> group by sdept;
结果如下。
+--------+-------------+ |sdept |count_sdept| +--------+-------------+ |信管 | 1| |经贸 | 1| |计算机| 2| +--------+-------------+ 3 rows in set (0.01 sec)
该查询结果根据sdept的值进行分组,sdept值相同的为一组,分为三组,然后对每一组进行行数统计,得到每一组的人数。
2.多字段分组
GROUP BY 也可以根据多个字段进行分组,如范例8-28所示。
【范例8-28】
查询不同性别不同年龄的学生人数。
MySQL> select ssex,sage,count(*) -> from student -> group by ssex,sage;
结果如下。
+------+------+----------+ | ssex | sage | count(*) | +------+------+----------+ |女 | 19| 1| |女 | 21| 1| |男 | 20| 1| |男 | 21| 1| +------+------+----------+ 4 rows in set (0.00 sec)
该查询先根据性别分成两组,然后在女生中根据年龄再次分组,男生中也根据年龄再次分组。
3.GROUP BY与HAVING子句一起使用
如果查询结果只想输出满足某种指定条件的组,要使用HAVING子句对组进行筛选,得到符合条件的组的信息。
【范例8-29】
查询平均成绩大于80分的学生编号和平均成绩。
MySQL> select sno,avg(grade) -> from sc -> group by sno -> having avg(grade)>80; 结果如下。 +------+------------+ |sno |avg(grade)| +------+------------+ | 1| 84.3333| | 2| 84.0000| +------+------------+ 2 rows in set (0.08 sec)
该查询的过程为根据sno将学生选修课程的信息分组,然后统计每组的平均成绩,最后删选出平均成绩大于80分的组的信息。
4.GROUP BY子句与GROUP_CONCAT()函数一起使用
GROUP BY子句还可以和GROUP_CONCAT()函数一起使用,GROUP_CONCAT()函数返回一个字符串结果,该结果由分组中的值连接组合而成。
【范例8-30】
查询每个学生的各科成绩,成绩在一行显示。
MySQL> select sno,group_concat(grade) -> from sc -> group by sno;
结果如下。
+------+---------------------+ |sno |group_concat(grade)| +------+---------------------+ | 1|89,97,67 | | 2|78,90 | +------+---------------------+ 2 rows in set(0.00 sec)
5.GROUP BY子句使用ROLLUP
【范例8-31】
查询每个学生的最高分和最低分,并统计所有学生的最高分和最低分。
MySQL> select sno,max(grade),min(grade) -> from sc -> group by sno -> with rollup;
结果如下。
+------+------------+------------+ |sno |max(grade)|min(grade)| +------+------------+------------+ | 1| 97| 67| | 2| 90| 78| |NULL| 97| 67| +------+------------+------------+
使用WITH ROLLUP之后,该查询的最后结果出现一行。该行中分组列没有值,GROUP BY后面分组的列,则根据前面的MAX函数和MIN函数做了处理。得到后两列对应的最大值和最小值。
提示 返回结果的SELECT子句字段中,这些字段要么包含在GROUP BY语句的后面,作为分组的依据;要么就要被包含在聚合函数中,作为函数的参数。