SQL 分组查询

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