测试必备的Mysql常用sql语句系列
https://www.cnblogs.com/poloyy/category/1683347.html
having
GROUP BY <字段名>[,<字段名>,<字段名>]
对sex单个字段进行分组查询
select * from yyTest group by sex;
分组之后,只会返回组内第一条数据;具体原理可以看看下图
先按照age进行分组,然后再在每个组内按department分组
select * from yyTest group by age,department;
group_concat()可以将分组后每个组内的值都显示出来
select department,group_concat(username) as "部门员工名字" from yyTest group by department;
可以看到,按department部门分组 ,然后查看每个部门都有哪些员工的名字;还是很便捷的
# count统计条数
select count(*) from yyTest group by department;
# sum总和
select sum(age) from yyTest group by department;
# max最大值
select max(age) from yyTest group by department;
# min最小值
select min(age) from yyTest group by department;
# 平均值
select avg(age) from yyTest group by department;
with rollup用来在所有记录的最后加上一条记录,显示上面所有记录每个字段的总和(不懂的直接看栗子)
select GROUP_CONCAT(username) from yyTest group by department with rollup;
select sum(age) from yyTest group by department with rollup;
select count(*) from yyTest group by department with rollup ;