增强group by的使用

 

-- (1) 显示某种岗位的雇员总数、平均工资
SELECT COUNT(empno),FORMAT(AVG(sal),2) AS avg_sal,job
	FROM emp
	GROUP BY job;

-- (2) 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*) AS emp_total,COUNT(comm) AS emony
	FROM emp;

 

-- (3) 统计获得补助的非空补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL,1,NULL))
	FROM emp;
SELECT COUNT(*),COUNT(*)-COUNT(comm)
	FROM emp;

 

-- (4) 显示管理者的总人数
SELECT COUNT(DISTINCT mgr) FROM emp;

-- (5) 显示雇员工资的最大差额
SELECT MAX(sal)-MIN(sal) FROM emp; 

# 应用案例：请统计各个部门group by 的平均工资 avg，
-- 并且是大于1000的 having ，并且按照平均工资从高到低排序，order by
-- 取出前两行记录 limit
SELECT deptno,AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno
	HAVING avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0,2;