一、组函数和group by 子句的基础
①将多条记录组合在一起,产生一个输出,即函数的输入有N条, 输出仅有一条;
②代表函数有: mix, min, sum, count, avg
③在有group by子句的SQL语句中,所有出现在select后面的字段,如果没有出现在group by 子句后面,则必须出现在组函数中。如: select deptno, max(sal) from emp group by deptno;
二、子查询语句汇总
1. 查询每个部门工资最高的人的信息:
方法一:
select e.deptno,e.ename,e.sal from emp e,
(select deptno,max(sal) sal from emp group by deptno) t
where e.deptno=t.deptno and e.sal=t.sal order by e.deptno;
方法二:
select ename,e.sal,e.deptno from emp e join
(select max(sal) sal, deptno from emp group by deptno) t
on e.sal=t.sal and e.deptno=t.deptno;
注意:inner join ... on...相当于join... on...,为了让where只做选择条件,on做连接条件,推荐使用第二种方法进行表的连接操作。
2、查询每个员工姓名、部门名称、工资等级,并筛选以A开头的名字:
select ename, dname, grade from emp e join dept d on e.deptno=d.deptno join salgrade on e.sal between losal and hisal where ename like 'A%';
3、 求部门平均薪水的等级:
select e.deptno,d.dname, e.avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) e
join dept d on e.deptno=d.deptno join salgrade s on avg_sal between s.losal and hisal;
4、 雇员中有哪些人是经理人: select ename 经理人 from emp where empno in (select distinct mgr from emp);
①只查询经理即可;
②父查询和子查询中,如果没有相同的查询属性值,则不会起冲突,可不起别名;
③利用distinct过滤掉重复的值;
5、 不准用组函数,求薪水的最高值:
select e.sal from emp e where e.sal not in
( select distinct e1.sal from emp e1 join emp e2 on e1.sal<e2.sal);
①首先将emp表自连接,找出所有的左边的表的sal值小于右边的表的sal值,进行一次类似于笛卡尔积的连接;
②左边表的最大值在右边表中找不到适合的匹配,被遗漏出来,我们捡漏即可找出最大值;
③因为此连接时类似于笛卡尔积的连接,左边的一条记录会同时和右边的多条记录进行连接,所以需要利用distinct过滤掉重复的值;
6、 求平均薪水最高的部门的部门编号:
方法一:
select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal=
(select max(avg_sal) from
(select avg(sal) avg_sal from emp group by deptno));
方法二:
select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal=
(select max( avg(sal)) from emp group by deptno);
①先求出每个部门的平均工资,以及部门编号;
②根据求出来的平均工资,计算平均工资里面的最大值,而后利用该最大值与每一个部门的平均工资进行比较,即可求出平均工资最高点的部门编号;
7、 求平均薪水最高的部门的部门名称:
select e.deptno,d.dname ,avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) e
join dept d on e.deptno=d.deptno where avg_sal=
(select max(avg_sal) from
(select avg(sal) avg_sal from emp group by deptno));
①子查询中相同的属性值可以不起别名不指定,但是在连接查询中,如果两个表有相同的属性列,必须确切指定属性列属于哪张表。
8、 求平均薪水的等级最低的部门的部门名称:
select t.deptno, dname, avg_sal, grade from
(
select deptno, avg_sal, grade from
(
select deptno,avg(sal) avg_sal from emp group by deptno
)e
join salgrade on (avg_sal between losal and hisal)
)t
join dept d on d.deptno=t.deptno
where grade=
(
select min(grade) from
(
select deptno, avg_sal, grade from
(
select deptno,avg(sal) avg_sal from emp group by deptno
)e
join salgrade on (avg_sal between losal and hisal)
)
);
①可以使用视图来简化该SQL语句,视图就是一个子查询或者说就是一张虚表,原来是从表(子查询)里取数据,现在从视图
里面取数据;
②由于当下使用scott用户来操作数据表,其没有创建表的权限,我们先登录系统用户sys/ system,为其分配创建表的权限后再登录scott用户,创建视图;
③从视图中取数据,简化如上代码如下:
select t.deptno, dname, avg_sal, grade from v$_dept_avg_sal_info t
join dept d on t.deptno=d.deptno where t.grade=
(select min(grade) from v$_dept_avg_sal_info);
9、 求比普通员工的最高薪水还要高的经理人姓名:
方法一:
select empno,ename,sal from emp where empno in
(select distinct mgr from emp where mgr is not null)
and sal>
(select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null));
方法二:
select distinct m.empno, m.ename, m.sal from
(select e1.empno, e1.ename, e1.sal from emp e1 join emp e2 on e1.empno=e2.mgr) m
where m.sal>
(select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null)
);
方法三:
select empno,ename,sal from emp where empno in
(select distinct mgr from emp where mgr is not null)
and sal>
(select max(sal) from
(select sal from emp where empno not in
(select distinct mgr from emp where mgr is not null)
)
);
10、 求薪水最高的前五名雇员:
select ename, sal from
(select ename,sal from emp where sal is not null order by sal desc)
where rownum<=5;
错误写法:
11、 求薪水最高的第6到第10名雇员:
select R, ename, sal from
(select rownum R, ename,sal from
(select ename, sal from emp where sal is not null order by sal desc)
)
where R>=6 and R<=10;
①这种三层select嵌套方法在Oracle里面处理此类问题时,是效率最高的;
12、 求个部门的平均薪水等级: select t.deptno,avg(t.grade) from (select deptno,ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) t group by t.deptno;
①先求出公司中所有人的工资等级和所属部门编号,再根据部门编号分组,将等级作为基数求平均值。