本节主要内容：
oracle中子查询与连接查询的综合实例。
1, 求部门中那些人的薪水最高
复制代码 代码示例:
select e.deptno, e.ename, e.sal
from (select deptno, max(sal) max_sal from emp group by deptno) t
join emp e on (e.deptno = t.deptno and e.sal = t.max_sal)
2, 求部门平均薪水的等级
I , 先求出部门的平均薪水
II, 再求其平均薪水的等级
复制代码 代码示例:
select d.deptno, avg_sal, grade from dept d
join (select deptno, avg(sal) avg_sal from emp group by deptno) t
on (t.deptno = d.deptno)
join salgrade s on (t.avg_sal between losal and hisal)
3, 求部门平均的薪水等级
I , 先求出所有人的薪水等级
II, 再求薪水等级的平均值
复制代码 代码示例:
select e.deptno, avg(s.grade) from emp e
join salgrade s on (e.sal between s.losal and s.hisal)
group by e.deptno
4, 雇员中有哪些人是经理人
① in
I , 先求出所有经理人的编号mgr
II, 在根据e1.empno = e2.mgr 求出经理人
复制代码 代码示例:
select ename
from emp
where empno in (select distinct mgr from emp)
② 连接查询
复制代码 代码示例:
select e1.empno, e1.ename, e2.empno, e2.mgr
from emp e1
join emp e2 on (e1.empno = e2.mgr)
5, 不用组函数, 求薪水的最高值
①
左表薪水最高的那条记录 匹配不到, 对应的e2.sal = null
复制代码 代码示例:
select e1.ename, e1.sal from emp e1
left join emp e2 on (e1.sal < e2.sal)
where e2.sal is null
and e1.sal is not null
② 先求出能比别人小的薪水集合, 不在该集合的薪水即为最大薪水
复制代码 代码示例:
select ename, sal from emp
where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal))
6, 平均薪水最高的部门的编号
I , 先求部门平均薪水
II , 再求部门平均薪水的最大值
III, 最后求出部门平均薪水最大值的部门编号
①
复制代码 代码示例:
select deptno, avg(sal) from emp
group by deptno
having avg(sal) =
(
select max(avg_sal)
from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
)
②
复制代码 代码示例:
select deptno, avg(sal) from emp
group by deptno
having avg(sal) =
(select max(avg(sal)) from emp group by deptno)
7, 平均薪水的等级最低的部门的部门名称
I , 先求部门的平均薪水
II , 再求其平均薪水的等级
III, 进而求出最低的等级
VI , 最后求部门名称
复制代码 代码示例:
select d.deptno, d.dname, tt.grade, tt.avg_sal from dept d
join
-- 部门平均薪水对应的等级
(select t.deptno, t.avg_sal, s.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)
) tt
on (d.deptno = tt.deptno)
where tt.grade =
(
-- 最低的薪水等级
select min(tt.grade) from
-- 部门平均薪水对应的等级
(select t.deptno, t.avg_sal, s.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)
) tt
)
8, 求部门经理人中平均薪水最低的部门名称
复制代码 代码示例:
select d.dname, t2.avg_sal from
(
-- 经理人按部门分组的平均薪水
select t.deptno, avg(sal) avg_sal from
( -- 经理人的编号,工资,部门
select distinct e1.empno, e1.sal, e1.deptno from emp e1
join emp e2 on e1.empno = e2.mgr
) t
group by t.deptno
) t2
join dept d on t2.deptno = d.deptno
where t2.avg_sal =
(
select min(t2.avg_sal) from
( -- 经理人按部门分组的平均薪水
select t.deptno, avg(sal) avg_sal from
( -- 经理人的编号,工资,部门
select distinct e1.empno, e1.sal, e1.deptno from emp e1
join emp e2 on e1.empno = e2.mgr
) t
group by t.deptno
) t2
)
9, 求比普通员工的最高薪水还高的经理人名称
复制代码 代码示例:
select empno, ename, sal from emp
where empno in (select distinct mgr from emp)
and sal >
(
--普通员工的最高薪水
select max(sal) from emp
where empno not in
( -- 经理人的编号
select distinct mgr from emp where mgr is not null
)
)
10, 求薪水最高的前5名雇员
复制代码 代码示例:
select ename, sal
from (select ename, sal from emp 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 order by sal desc) t1
) t2
where r >=6 and r <= 10
12, 比较效率
复制代码 代码示例:
①select * from emp where deptno = 10 and ename like '%A%';
②select * from emp where ename like '%A%' and deptno = 10;
解:
第一个效率高,
13, 有三个表
S(SNO, SNAME) 学生表(学号, 姓名)
C(CNO, CNAME, CTEACHER) 老师表(课号, 课名, 教师)
SC(SNO, CNO, SCGRADE) 选课表(学号, 课号, 成绩)
问题
1, 找出没选过"黎明"老师的所有学生姓名
复制代码 代码示例:
select S.SNAME
from S
join SC on (S.SNO = SC.SNO)
join C on (SC.CNO = C.CNO)
where C.CTEACHER <> '黎明'
2, 列出2门以上(含2门)不及格学生 姓名及平均成绩
复制代码 代码示例:
select S.SNAME, avg(SC.SCGRADE) avg_grade
from S
join SC on (S.SNO = SC.SNO)
where SC.SNO in
( -- 2门及以上不及格的学生编号
select SC.SNO
from SC
where SC.SCGRADE < 60
group by SC.SNO
having count(*) >= 2
)
group by S.SNO, S.SNAME
3, 既学过1号课程又学过2号课程所有学生的姓名
复制代码 代码示例:
select S.SNAME from S
where S.SNO in
(-- 学过 1 和 2 课程的学生编号
select sc1.SNO
from SC sc1
join SC sc2 on (sc1.CNO = sc2.CNO)
where sc1.CNO = 1 and sc2.CNO = 2
)
14,一个简单的表TABLE 有100条以上的信息，其中包括：
产品 颜色 数量
产品1 红色 123
产品1 蓝色 126
产品2 蓝色 103
产品2 红色 NULL
产品2 红色 89
产品1 红色 203
。。。。。。。。。。。。
请用sql语句完成以下问题：
(1) 按产品分类，将数据按下列方式进行统计显示
产品 红色 蓝色
(2) 按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量：
复制代码 代码示例:
create table product
(
name varchar2(10),
color varchar2(10),
alinuxjishu/9952.html target=_blank class=infotextkey>mount number(4)
);
insert into product values('产品1', '红色', 123);
insert into product values('产品1', '蓝色', 126);
insert into product values('产品2', '蓝色', 103);
insert into product values('产品2', '红色', NULL);
insert into product values('产品2', '红色', 89);
insert into product values('产品1', '红色', 203);
create view v$product
as
select name,
sum(case when color = '红色' then amount else 0 end) red_amount,
sum(case when color = '蓝色' then amount else 0 end) blue_amount
from product
group by name;
select * from v$product;
NAME RED_AMOUNT BLUE_AMOUNT
----- ---------- -----------
产品1 326 126
产品2 89 103
select name, (red_amount - blue_amount) difference_amount
from v$product
where red_amount > blue_amount;
NAME DIFFERENCE_AMOUNT
----- -----------------
产品1 200
drop view v$product;
