(1) Group by语句 求薪水值最高的人的名字. select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配. 应如下求: select ename from emp where sal=(select max(sal) from emp);
Group by语句应注意,
出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中. (2) Having 对分组结果筛选 Where是对单条纪录进行筛选,Having是对分组结果进行筛选. select avg(sal),deptno from emp group by deptno having avg(sal)>2000; 查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列. select * from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc; (3):子查询 谁挣的钱最多(谁:这个人的名字, 钱最多) select 语句中嵌套select 语句,可以在where,from后 问那些人工资,在平均工资之上. select ename,sal from emp where sal>(select avg(sal) from emp);
查找每个部门挣钱最多的那个人的名字. select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查询会多值.
应该如下: select max(sal),deptno from emp group by deptno;当成一个表.语句如下: select ename, sal from emp join(select max(sal) max_sal,deptno from emp group by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno); 每个部门的平均薪水的等级. 分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.(4) join on
三张表连接: slect ename,dname, grade from emp e join dept d on(e.deptno=d.deptno) join salgrade s on(e.sal between s.losal and s.hisal) where ename not like '_A%'; 把每张表连接 条件不混在一起,然后数据过滤条件全部区分开来。读起来更清晰,更容易懂一点。 select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);
左外连接:会把左边这张表多余数据显示出来。 select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer 右外连接: select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。 即把左边多余数据,也把右边多余数据拿出来,全外连接。 select ename,dname from emp e full join dept d on(e.deptno =d.deptno);
(5) 面试中可能出现的问题:
a.求部门平均薪水的等级。
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno)t join salgrade s on(t.avg_sal between s.losal and s.hisal) b.求部门平均的薪水等级 select deptno,avg(grade) from (select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and s.hisal)) t group by deptno
c.那些人是经理 select ename from emp where empno in(select mgr from emp); select ename from emp where empno in(select distinct mgr from emp); d.不准用组函数,求薪水的最高值(面试题) select distinct sal from emp where sal not in
( select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal)
); e.平均薪水最高的部门编号 select deptno,avg_sal from (select avg(sal)avg_sal,deptno from emp group by deptno) where avg_sal= (
select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) ) f.平均薪水最高的部门名称 select dname from dept where deptno= ( select deptno from (select avg(sal)avg_sal,deptno from emp group by deptno) where avg_sal= ( select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) ) ) g.求平均薪水的等级最低的部门的部门名称 组函数嵌套 如:平均薪水最高的部门编号,可以E.更简单的方法如下: select deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg(sal)) from emp group by deptno) 组函数最多嵌套两层 分析: 首先求 1.平均薪水: select avg(sal) from group by deptno;
2.平均薪水等级: 把平均薪水当做一张表,需要和另外一张表连接salgrade select deptno,grade avg_sal from ( select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal) 上面结果又可当成一张表。 DEPTNO GRADE AVG_SAL -------- ------- ---------- 30 3 1566.66667 20 4 2175 10 4 2916.66667
3.求上表平均等级最低值 select min(grade) from ( select deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno)t join salgrade s on(t.avg_sal between s.losal and s.hisa) )
4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。 select dname ,deptno,grade,avg_sal from ( select deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno)t join salgrade s on(t.avg_sal between s.losal and s.hisal) ) t1 join dept on (t1.deptno = dept.deptno) where t1.grade = ( select deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal) ) ) 结果如下: DNAME DEPTNO GRADE AVG_SAL -------- ------- -------- -------- SALES 30 3 1566.6667 h: 视图(视图就是一张表,一个字查询) G中语句有重复,可以用视图来简化。 conn sys/bjsxt as sysdba; grant create table,create view to scott; conn scott/tiger 创建视图: create view v$_dept_avg-sal_info as select deptno,grade,avg_sal from ( select deptno,avg(sal) avg_sal from emp group by deptno)t join salgrade s on 9t.avg_sal between s.losal and s.hisal) 然后 select * from v$_dept_avg-sal_info 结果如下: DEPTNO GRADE AVG_SAL -------- ------- ---------- 30 3 1566.66667 20 4 2175 10 4 2916.66667
然后G中查询可以简化成: select dname,t1.deptno,grade,avg_sal from v$_dept_avg-sal_info t1 join dept on9t1.deptno =dept.deptno) where t1.grade= ( select min(grade) from v$_dept_avg-sal_info t1 )