一、分组
group by ,将符合条件的记录,进一步分组
-
使用说明:
- select 出现分组函数,就不能使用非分组信息(因为没用),可以使用group by 字段。
- select 出现的字段,group by 必须出现。 group by 出现的字段,select 可以不出现。
-
现在查询的完整结构如下:
select distinct * |字段|表达式|函数 as 别名 from 表 表别名 where 过滤行记录条件 group by 分组字段列表 having 过滤组 order by 字段列表 asc | desc
# 按照部门 查询平均工资
select avg(sal) from emp GROUP BY deptno;
8494.5000
2916.6667
2175.0000
1566.6667
select avg(sal),deptno from emp GROUP BY deptno;
8494.5000
2916.6667 10
2175.0000 20
1566.6667 30
#按 部门 查询 平均工资
select avg(sal),deptno from emp group by deptno;
8494.5000
2916.6667 10
2175.0000 20
1566.6667 30
#按 岗位 查询 平均工资
select avg(sal),job from emp group by job;
8494.5000
1037.5000 CLERK
1400.0000 SALESMAN
2758.3333 MANAGER
3000.0000 ANALYST
5000.0000 PRESIDENT
# 按 部门 岗位 查询 平均工资 每个部门和工作都是对应唯一性的
select avg(sal),deptno,job from emp group by deptno,job;
8494.5000
950.0000 20 CLERK
1400.0000 30 SALESMAN
2975.0000 20 MANAGER
2850.0000 30 MANAGER
2450.0000 10 MANAGER
3000.0000 20 ANALYST
5000.0000 10 PRESIDENT
950.0000 30 CLERK
1300.0000 10 CLERK
# 最低平均工资部门的员工信息 很重要 ,多练习这个 牵扯了很多子查询
# 1、各部门的平均工资
select avg(sal) from emp where 1=1 GROUP BY deptno;
8494.5000
2916.6667
2175.0000
1566.6667
select avg(sal),deptno from emp where 1=1 GROUP BY deptno;
8494.5000
2916.6667 10
2175.0000 20
1566.6667 30
# 2、最小的平均工资
select min(t.avgsal) from (select avg(sal) as avgsal from emp where 1=1 GROUP BY deptno) as t where 1=1;
1566.6667
# 3、找到最低部门
select t2.deptno from (
select avg(sal) as avgsal,deptno from emp where 1=1 GROUP BY deptno
)
as t2 where t2.avgsal = (select min(t.avgsal) from (select avg(sal) as avgsal from emp where 1=1 GROUP BY deptno) as t where 1=1
);
30
# 4、 找到对应的员工
select * from emp where 1=1 and deptno = (select t2.deptno from (
select avg(sal) as avgsal,deptno from emp where 1=1 GROUP BY deptno
)
as t2 where t2.avgsal = (select min(t.avgsal) from (select avg(sal) as avgsal from emp where 1=1 GROUP BY deptno) as t where 1=1
));
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 0
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1
7698 BLAKE MANAGER 7839 1981-05-01 2850 30 1
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 950 30 0
二、having
按 部门查询平均工资,且平均工资大于2000的部门编号
(1)不使用 having
select deptno,avgsal from (
select avg(sal) avgsal ,deptno from emp where 1=1 group by deptno)
t where t.avgsal >2000;
select avgsal,deptno from (
select avg(sal) avgsal ,deptno from emp where 1=1 group by deptno)
t where t.avgsal >2000;
8494.5000
2916.6667 10
2175.0000 20
(2)使用having ,过滤
where : 过滤行记录,不能使用组函数。
having:过滤组可以使用组函数。
#可以使用selcet 后边的字段进行条件判断。
select avg(sal) avgsal ,deptno from emp where 1=1 GROUP BY deptno having avgsal>2000;
8494.5000
2916.6667 10
2175.0000 20
# 使用组函数
select deptno from emp where 1=1 GROUP BY deptno having avg(sal)>2000;
10
20
#