mysql之分组查询group by

时间:2022-03-17 01:29:51

格式:group by 属性【having 条件表达式】【with rollup】

1,单独使用,没有什么意义
2,与group_concat()函数一起使用
例如:
SELECT gradeName, GROUP_CONCAT(stuName)FROM t_student GROUP BY gradeName
mysql之分组查询group by

3,与聚合函数一起使用。(如:sum,count,avg)
例如:
SELECT gradeName, COUNT(stuName)FROM t_student GROUP BY gradeName
mysql之分组查询group by

4,与having一起使用,限制输出结果 可以加上别名
例如:
SELECT gradeName, COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName) >3
mysql之分组查询group by
加上别名
SELECT gradeName, COUNT(stuName) num FROM t_student GROUP BY gradeName HAVING num >3
mysql之分组查询group by

5,与with rollup 一起使用 在随后加入一个总和行
数字相加,字符串相连。
例如:
SELECT gradeName,COUNT(stuName),GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP
mysql之分组查询group by