对于嵌套子查询的练习
–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, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 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)) 语文,
max(decode(ss.subject, '数学', ss.score)) 数学,
max(decode(ss.subject, '英语', 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;