SQL 分组查询

 1 # 分组计算各部门的工资平均值
 2 SELECT deptno,ROUND(AVG(sal)) FROM t_emp GROUP BY deptno;
 3 # 多重分组
 4 SELECT deptno,job,COUNT(*),ROUND(AVG(sal)) FROM t_emp GROUP BY deptno,job ORDER BY deptno;
 5 # 再次汇总
 6 SELECT deptno,AVG(sal),SUM(sal),MAX(sal),MIN(sal),COUNT(*) FROM t_emp GROUP BY deptno WITH ROLLUP;
 7 # 查询各部门超过两千的员工
 8 SELECT deptno,COUNT(*),GROUP_CONCAT(ename) FROM t_emp WHERE sal>=2000 GROUP BY deptno;
 9 # 查询平均工资超过2000的部门编号
10 SELECT deptno,AVG(sal) FROM t_emp GROUP BY deptno HAVING AVG(sal)>=2000;
11 # 查询每个部门中，1982年以后入职人数2人以上的部门编号(HAVING 用聚合函数做条件判断)
12 SELECT deptno,COUNT(*) FROM t_emp WHERE hiredate>="1982-01-01" GROUP BY 1 HAVING COUNT(*)>=2;