对于嵌套子查询的练习
–1.求部门中薪水最高的人
select * from emp e join (select max(e.sal) max from emp e group by e.deptno) m on m.max = e.sal;
–2.求部门平均薪水的等级
select m.*,sg.grade from (select avg(e.sal) sal,e.deptno from emp e group by e.deptno) m join salgrade sg on m.sal between sg.losal and sg.hisal;
–3.求部门平均的薪水等级
select m.*,sg.grade from (select avg(m.avg) sal from (select avg(e.sal) avg,e.deptno dp from emp e group by e.deptno) m) m join salgrade sg on m.sal between sg.losal and sg.hisal;
–4.雇员中有哪些人是经理人
select * from (select distinct e.mgr no from emp e where e.mgr is not null) m join emp e on e.empno = m.no;
–5.不准用组函数，求薪水的最高值
select e1.* from emp e1 where e1.sal >= all (select e.sal sal from emp e);
–6.求平均薪水最高的部门的部门编号
select e.dp from (select max(m.avg) max from (select avg(e.sal) avg from emp e group by e.deptno) m) m join (select avg(e.sal) avg,e.deptno dp from emp e group by e.deptno) e on e.avg = m.max;
–组函数嵌套写法(对多可以嵌套一次，group by 只对内层函数有效)
–7.求平均薪水最高的部门的部门名称
select d.dname from (select e.dp dp from (select max(m.avg) max from (select avg(e.sal) avg from emp e group by e.deptno) m) m join (select avg(e.sal) avg,e.deptno dp from emp e group by e.deptno) e on e.avg = m.max) m join dept d on d.deptno = m.dp ;
–8.求平均薪水的等级最低的部门的部门名称
select d.dname from (select n.dp dp from (select max(m.avg) max from (select avg(e.sal) avg from emp e group by e.deptno) m) m join (select avg(e.sal) avg,e.deptno dp from emp e group by e.deptno) n on m.max = n.avg) m join dept d on d.deptno = m.dp;
–9.求部门经理人中平均薪水最低的部门名称
select d.dname from (select n.dp dp from (select min(m.avg) min from (select avg(m.sal) avg from (select e.sal sal,e.deptno dp from (select distinct e.mgr no from emp e where e.mgr is not null) m join emp e on e.empno = m.no) m group by m.dp) m) m join (select avg(m.sal) avg,m.deptno dp from (select e.sal sal,e.deptno deptno from (select distinct e.mgr no from emp e where e.mgr is not null) m join emp e on e.empno = m.no) m group by m.deptno) n on m.min = n.avg) m join dept d on d.deptno = m.dp;
–10.求比普通员工的最高薪水还要高的经理人名称(not in)
select e.ename from (select m.no no from (select e.sal sal,e.empno no from (select distinct e.mgr no from emp e where e.mgr is not null) m join emp e on e.empno = m.no) m join (select max(m.sal) max from (select e.sal sal from emp e where e.empno not in (select distinct e.mgr no from emp e where e.mgr is not null)) m) n on m.sal > n.max) m join emp e on e.empno = m.no;
–11.求薪水最高的前5名雇员
select m.* from (select rownum r,m.* from (select * from emp e order by e.sal desc) m) m where m.r <= 5;
–12.求薪水最高的第6到第10名雇员(important)
select m.* from (select rownum r,m.* from (select * from emp e order by e.sal desc) m) m where m.r > 5 and m.r <= 10;
–13.求最后入职的5名员工
select m.* from (select rownum r,m.* from (select * from emp e order by e.hiredate desc) m) m where m.r <= 5;
对于以上习题，肯定还有更好和更简便的实现方式，本人只是练习使用嵌套子查询的使用，个人觉得这种查询很基础。
下面再多写个行转列吧
题目：
建表
create table STUDENT_score ( name VARCHAR2(20),subject VARCHAR2(20),score NUMBER(4,1) )
– 添加数据
insert into student_score (NAME,SUBJECT,score) values ('张三','语文',78.0);
insert into student_score (NAME,'数学',88.0);
insert into student_score (NAME,'英语',98.0);
insert into student_score (NAME,score) values ('李四',89.0);
insert into student_score (NAME,76.0);
insert into student_score (NAME,90.0);
insert into student_score (NAME,score) values ('王五',99.0);
insert into student_score (NAME,66.0);
insert into student_score (NAME,91.0);
– 希望得到下面的结果
– 姓名 语文 数学 英语
– 王五 89 56 89
使用case when then end
select ss.name,max(case when ss.subject = '语文' then ss.score end) 语文,max(case when ss.subject = '数学' then ss.score end) 数学,max(case when ss.subject = '英语' then ss.score end) 英语 from student_score ss group by ss.name;
使用decode
select ss.name,max(decode(ss.subject,ss.score)) 语文,ss.score)) 数学,ss.score)) 英语 from student_score ss group by ss.name;
使用多条子查询
select m1.name,m1.sc,m2.sc,m3.sc from (select ss.name,ss.score sc from student_score ss where ss.subject = '语文') m1 join (select ss.name,ss.score sc from student_score ss where ss.subject = '数学') m2 on m1.name = m2.name join (select ss.name,ss.score sc from student_score ss where ss.subject = '英语') m3 on m2.name = m3.name;
