7.分组 group by

分组group by

-- 分组group by
-- 统计各个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
-- 字段和多行函数不可以同时使用,除非这个字段属于分组

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno DESC;

-- 统计各个岗位的平均工资
SELECT job,AVG(sal) FROM emp GROUP BY job;
SELECT job,LOWER(job),AVG(sal) FROM emp GROUP BY job;

-- 统计各个部门的平均工资,只显示平均工资2000以上的 - 分组以后进行二次1筛选 having
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;
SELECT deptno,AVG(sal) 平均工资 FROM emp GROUP BY deptno HAVING 平均工资 > 2000;
SELECT deptno,AVG(sal) 平均工资 FROM emp GROUP BY deptno HAVING 平均工资 > 2000 ORDER BY deptno DESC;

-- 统计各个岗位的平均工资,除了MANAGER
-- 方法1:
SELECT job,AVG(sal) FROM emp WHERE job != 'MANAGER' GROUP BY job;

-- 方法2:
SELECT job,AVG(sal) FROM emp GROUP BY job HAVING job != 'MANAGER';

-- where 在分组前进行过滤的,having 在分组后进行过滤

1.select 语句总结
SELECT COLUMN , group_function(COLUMN)
FROM TABLE
[WHERE CONDITION]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY COLUMN];

注意:顺序固定,不可以改变顺序

2.select 语句的执行顺序
FROM - WHERE - GROUP BY - SELECT - HAVING - ORDER BY


-- 单表查询练习
-- 列出工资最小值小于2000的职位
SELECT job,MIN(sal)
FROM emp
GROUP BY job
HAVING MIN(sal) <2000;

-- 列出平均工资大于1200元的部门和工作搭配组合
SELECT deptno,job,AVG(sal)
FROM emp
GROUP BY deptno,job
HAVING AVG(sal) > 1200
ORDER BY deptno;


-- 统计人数小于4的部门的平均工资
SELECT deptno,COUNT(1),AVG(sal)
FROM emp
GROUP BY deptno
HAVING COUNT(1) < 4;

-- 统计各部门的最高工资,排除最高工资小于3000的部门
SELECT deptno,MAX(sal)
FROM emp
GROUP BY deptno
HAVING MAX(sal) < 3000;