MySQL数据库分组及分组后过滤详解

　　一天一个关于测试知识点，5分钟内讲解你最关心的软件测试问题，今天就接着来谈谈关于软件测试中的“MySQL数据库分组及分组后过滤详解”。

　　一、分组的概念：

指定某列或某些列作为划分的依据,比较这些列值是否相同,具有相同列值的行放在同一组,这样就可以将最初的结果集划分为若干个子集,每个子集称为一个分组.

　　要想知道分组后每一组具体有什么数据使用排序去查，要知道有几个组，则用分组语句去查。

　　按照性别分组

　　select * from student order by ssex;

　　select ssex from student group by ssex;

　　按照年龄分组

　　select * from student order by sage;

　　select sage from student group by sage;

　　按照性别、年龄分组

　　select * from student order by ssex,sage;

　　select ssex,sage from student GROUP BY ssex,sage ;

　　按照年龄、性别分组

　　select ssex,sage from student GROUP BY sage,ssex ;

　　select DISTINCT ssex,sage from student;

　　注意事项：

　　1)在oracle中按照什么列分组，则select后边才可以写对应的列名，如果没有按照该列分组，则不能在select后边写该列名，mysql中没有此限制。

　　2)按照单列分组时，该列有多少种不同的值，就能将数据按照该列分成多少组。

　　3)按照多列分组时，多列的值有多少种不同组合，则就能分成多少组。同时按照多列分组时，列的顺序谁在前，谁在后，不会影响到分组的结果,只会影响到分组后每一组的顺序。

　　4)如果对分组后的数据进行统计，同时是按照组进行统计得。

　　二、分组后统计

　　单纯的对数据分组得到的最终效果其实和使用distinct去重效果类似，分组往往和统计函数一起使用才能真正体现出分组的价值。分组后统计是先对数据进行分组，分组后在select后边加上统计函数，这样统计函数就可以分别统计每一组的数据了。

　　查询每个部门有多少人，以及每个部门的平均工资。

　　select dname,ename,sal from emp right join dept on emp.deptno=dept.deptno order by 1;

　　select dname,avg(sal),count(empno) from emp right join dept on emp.deptno=dept.deptno group by 1;

　　统计参加考试的每个学生的总成绩，平均成绩，最高成绩，最低成绩，以参加了几门考试。

　　select * from student s,sc where s.sno=sc.sno order by s.sno;

　　select s.sno,sname,sum(score),avg(score),max(score),min(score),count(cno) from student s,sc where s.sno=sc.sno group by s.sno,sname;

　　统计每个科目的最高成绩，最低成绩，总成绩，平均成绩，以及参与该科目的学生人数。

　　select cname,score,sno from sc,course cc where sc.cno=cc.cno order by cname;

　　select cname,max(score),min(score),sum(score),avg(score),count(sno) from sc,course cc where sc.cno=cc.cno group by cname;

　　表列学生表中出男生女生的平均年龄，男生，女生的总人数;

　　select ssex,avg(sage),count(*) from student GROUP BY ssex;

　　找出平均工资最底的部门;

　　select * from (select dname,avg(sal) avl from emp,dept where emp.deptno=dept.deptno GROUP BY dname)t where t.avl=(select min(avl) from (select dname,avg(sal) avl from emp,dept where emp.deptno=dept.deptno GROUP BY dname)t);

　　三、对分组后的数据进行过滤

　　要对分组后的数据进一步过滤要在select 语句最后边加havig 过滤条件。

　　查询平均工资高于2000的部门有哪些?

　　子查询：

　　select * from (select dname,avg(sal) avl from emp,dept where emp.deptno=dept.DEPTNO GROUP BY dname)t where t.avl>2000;

　　分组后使用having过滤：

　　select dname,avg(sal) from emp,dept where emp.deptno=dept.DEPTNO GROUP BY dname having avg(sal)>2000;

　　查询所有成绩都在90分以上的学生。

　　子查询：

　　select * from (select s.sno,sname ,min(score) mis from student s,sc where s.sno=sc.sno GROUP BY s.sno,sname)t where t.mis>90;

　　分组后使用having过滤：

　　select s.sno,sname ,min(score) from student s,sc where s.sno=sc.sno GROUP BY s.sno,sname having min(score)>90;