14、oracle 多表查询，子查询，分页查询
多表查询，子查询，分页查询
select主句中的位置
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| select | 要返回的列或表达式 | 是 |
| from | 从中检索数据的表 | 仅在从表选择数据时使用 |
| where | 行级过滤 | 否 |
| group by | 分组说明 | 仅在按组计算聚集时使用 |
| Having | 组级过滤 | 否 |
| order by | 输出排序顺序 | 否 |
Sql语句执行过程：
- 读取from子句中的基本表、视图的数据，[执行笛卡尔积操作]。
- 选取满足where子句中给出的条件表达式的元组
- 按group子句中指定列的值分组，同时提取满足Having子句中组条件表达式的那些组
- 按select子句中给出的列名或列表达式求值输出
- Order by子句对输出的目标表进行排序。
多表查询
92语法
多表的联系都放在where后面，如果有n张表，条件就是n-1个，
会跟条件判断放在一起，可读性差。
--92语法
--等值连接
--查询出来的是两个表关联相等的部分。
--查询员工姓名，部门名称
select e.ename,d.dname
from emp e, dept d
where e.deptno = d.deptno;
--非等值连接
-- <,>,<=,>=,!=连接时称非等值连接
--查询员工姓名，薪资，薪资等级
select e.ename, e.sal, sg.grade
from emp e, salgrade sg
where e.sal between sg.losal and sg.hisal;
--外连接
--以＋号作为辅表，另一边作为主表，主表中的每一条记录都要显示。
--左外连接
select e.ename,d.deptno
from emp e, dept d
where e.deptno = d.deptno(+);
--右连接
select e.ename,d.deptno
from emp e, dept d
where e.deptno(+) = d.deptno;
--自连接
--两张表都指向同一张真实的表
--查询员工的上级领导是谁？
select e.ename||'的上司是'||mgr.ename
from emp e, emp mgr
where e.mgr = mgr.empno;
99语法
利用关键字，放在from后面，条件放在where后面，增强了代码的可读性。
CROSS JOIN 交叉连接（笛卡尔积）
NATURAL JOIN 自然连接
USING子句 括号后面跟的是两表相同的字段，如果不同，去笛卡尔积
ON子句 后面跟的是两表联系的条件
LEFT OUTER JOIN 左外连接：左边的表作为主表
RIGHT OUTER JOIN 右外连接：右边的表作为主表
FULL OUTER JOIN 取左右外连接的结果。
--99语法
--交叉连接（笛卡尔积）
select e.ename,d.dname
from emp e cross join dept d;
--自然连接（等值连接）
select e.ename,d.dname
from emp e natural join dept d;
--using创建连接
--括号里面就是两个表重复的列，如果没有就会显示笛卡尔积。
select deptno,e.ename,d.dname
from emp e join dept d using(deptno);
--on创建连接（后面接条件）
select e.ename, e.sal, sg.grade
from emp e
join salgrade sg
on e.sal between sg.losal and sg.hisal;
--外连接
--左外连接
--左边是主表
select e.ename,d.deptno from emp e left outer join dept d on e.deptno = d.deptno;
--右外连接
--右边是主表
select e.ename,d.deptno from emp e right outer join dept d on e.deptno = d.deptno;
select e.ename,d.dename,d.deptno
from emp e full outer join dept d on e.deptno = d.deptno;
子查询
SQL允许多层嵌套。子查询，即嵌套在其他查询中的查询。
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。
子查询要用括号括起来
将子查询放在比较运算符的右边(增强可读性)
--子查询
--放在where后面
--作为条件，单行数据
--查询最高薪资的员工信息
select e.* from emp e where e.sal = (select max(e.sal) from emp e);
--作为条件，结果是多行数据
--查询每个部门最高薪资的员工。
select e.*
from emp e
where e.sal in (select nvl(max(e.sal), 0) from emp e group by e.deptno);
--放在from后面作为一张新表，取个别名
--查询每个部门的平均薪资的等级。
select t01.dp,t01.vsal,sg.grade
from
(select nvl(e.deptno,0) dp,avg(e.sal) vsal
from emp e
group by e.deptno) t01, salgrade sg
where t01.vsal between sg.losal and hisal;
分页查询
--分页查询
--【1】查询前5天记录, rownum <=5
select e.* from emp e where rownum <= 5;
--【2】查询6~10条的记录
select t01.*
from (select e.*, rownum r from emp e) t01
where t01.r >= 6
and t01.r <= 10;
--【3】排序 查询工资最高的前5名
select t01.*
from (select e.* from emp e order by nvl(e.sal, 0) desc) t01
where rownum <= 5;
--【4】排序 查询工资在6~10的员工
select t02.*
from (select t01.*, rownum r
from (select e.* from emp e order by nvl(e.sal, 0) desc) t01) t02
where t02.r >= 6
and t02.r <= 10;
练习01
--1、求平均薪水最高的部门的部门编号
select e.deptno, nvl(avg(e.sal), 0)
from emp e
group by e.deptno;
having nvl(avg(e.sal), 0) >= all (select nvl(avg(e.sal), 0) from emp e group by e.deptno)
--2、求部门平均薪水的等级
select t01.deptno, t01.vsal, sg.grade
from (select e.deptno, nvl(avg(e.sal), 0) vsal
from emp e
group by e.deptno) t01,
salgrade sg
where t01.vsal between sg.losal and hisal;
--3、求部门平均的薪水等级
select t01.deptno, nvl(avg(t01.grade), 0)
from (select e.ename, e.deptno, sg.grade
from emp e, salgrade sg
where e.sal between sg.losal and sg.hisal) t01
group by t01.deptno;
--4、求薪水最高的前5名雇员
select t01.*
from (select e.* from emp e order by nvl(e.sal, 0) desc) t01
where rownum <= 5;
--5、求薪水最高的第6到10名雇员
select t02.*
from (select t01.*, rownum r
from (select e.* from emp e order by nvl(e.sal, 0) desc) t01) t02
where t02.r >= 6
and t02.r <= 10;
99语法练习
--使用99语法更改相应作业：
--1．列出所有雇员的姓名及其上级的姓名。
select e.ename, mgr.ename from emp e left join emp mgr on e.mgr = mgr.empno
--2．列出入职日期早于其直接上级的所有雇员。
select e.ename 下级, e.hiredate, mgr.ename 上级, mgr.hiredate
from emp e
left join emp mgr
on e.mgr = mgr.empno
where e.hiredate < mgr.hiredate
--3．列出所有部门名称及雇员
select d.deptno,d.dname,e.ename
from dept d left join emp e on d.deptno = e.deptno;
--4．列出所有“CLERK”（办事员）的姓名及其部门名称。
select e.ename, d.dname, e.job, d.deptno
from emp e
left join dept d
on e.deptno = d.deptno
where e.job like 'CLERK'
--5．列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号。
select e.*, t01.dname
from (select d.* from dept d where d.dname like 'SALES') t01
join emp e
on t01.deptno = e.deptno
--6．列出在每个部门工作的雇员的数量以及其他信息。
select d.deptno,d.dname,t01.c
from
dept d full outer join
(select e.deptno,count(*) c
from emp e
group by e.deptno)t01 on d.deptno = t01.deptno
--7．列出所有雇员的雇员名称、部门名称和薪金。
select e.ename, d.dname, e.sal
from emp e
left join dept d
on e.deptno = d.deptno
--8.求出部门编号为20的雇员名、部门名、薪水等级
select e.deptno, e.ename, d.dname, sg.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade sg
on e.sal between sg.losal and sg.hisal
where e.deptno = 20;
行转列面试题
总结出来两种情况：
一种是每一个字段都形成一张表，这样做代码会很多，不过容易理解。
--形成多张表
select name.value 姓名, sex.value 性别, age.value 年龄
from (select t.t_id, t.value from test t where type = 1) name,
(select t.t_id, t.value from test t where type = 2) sex,
(select t.t_id, t.value from test t where type = 3) age
where name.t_id = sex.t_id
and sex.t_id = age.t_id;
一种是利用组函数，找出其中的关系，一般用decode,case when,最后用组函数，这样代码量少，难理解。
--利用组函数
select t.t_id,
max(decode(t.type, 1, value, 0)) 姓名,
max(decode(t.type, 2, value, 0)) 性别,
max(decode(t.type, 3, value, 0)) 年龄
from test t
group by t.t_id;
/**1 中国移动sql面试题：
create table test(
id number(10) primary key,
type number(10) ,
t_id number(10),
value varchar2(5)
);
insert into test values(100,1,1,'张三');
insert into test values(200,2,1,'男');
insert into test values(300,3,1,'50');
--
insert into test values(101,1,2,'刘二');
insert into test values(201,2,2,'男');
insert into test values(301,3,2,'30');
insert into test values(102,1,3,'刘三');
insert into test values(202,2,3,'女');
insert into test values(302,3,3,'10');
请写出一条查询语句结果如下：
姓名 性别 年龄
--------- -------- ----
张三 男 50
*/
select name.value 姓名, sex.value 性别, age.value 年龄
from (select t.t_id, t.value from test t where type = 1) name,
(select t.t_id, t.value from test t where type = 2) sex,
(select t.t_id, t.value from test t where type = 3) age
where name.t_id = sex.t_id
and sex.t_id = age.t_id;
-----------------------------------------------------------
select max(decode(type,1,value)) 姓名,
min(decode(type,2,value))性别,
min(decode(type,3,value)) 年龄
from test t
group by t_id
select * from test
--99语法
select t.t_id,
max(decode(t.type, 1, value, 0)) 姓名,
max(decode(t.type, 2, value, 0)) 性别,
max(decode(t.type, 3, value, 0)) 年龄
from test t
group by t.t_id;
------------------------------------------------------------------------------------------
/**2.一道SQL语句面试题，关于group by
表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
*/
select r.rq, s.sf 胜, f.sf 负
from (select t.rq from tmp t group by t.rq) r,
(select t.rq, count(t.shengfu) sf
from tmp t
where t.shengfu like '胜'
group by t.rq) s,
(select t.rq, count(t.shengfu) sf
from tmp t
where t.shengfu like '负'
group by t.rq) f
where r.rq = s.rq
and r.rq = f.rq
--99语法
select t.rq 日期,
sum(decode(t.shengfu, '胜', 1, 0)) 胜,
sum(decode(t.shengfu, '负', 1, 0)) 负
from tmp t
group by t.rq
------------------------------------------
create table tmp(rq varchar2(10),shengfu varchar2(5))
insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-10','胜')
insert into tmp values('2005-05-10','负')
insert into tmp values('2005-05-10','负')
/**3.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);
3.1得到类似下面的结果
姓名 语文 数学 英语
王五 89 56 89
*/
select n.name 姓名, yuwen.score 语文, shu.score 数学, ying.score 英语
from (select stu.name from STUDENT_SCORE stu group by stu.name) n,
(select stu.name, stu.subject, stu.score
from STUDENT_SCORE stu
where stu.subject like '语文') yuwen,
(select stu.name, stu.subject, stu.score
from STUDENT_SCORE stu
where stu.subject like '数学') shu,
(select stu.name, stu.subject, stu.score
from STUDENT_SCORE stu
where stu.subject like '英语') ying
where shu.name in n.name
and yuwen.name in n.name
and ying.name = n.name
--99语法
select stu.name 姓名,
max(decode(stu.subject, '语文', stu.score, 0)) 语文,
max(decode(stu.subject, '数学', stu.score, 0)) 数学,
max(decode(stu.subject, '英语', stu.score, 0)) 英语
from student_score stu
group by stu.name
----------------------------------------------------------------------------------------
/**3.2有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
------------------------------------------
*/
select case when t.语文 >=80 then '优秀'
when t.语文>=60 and t.语文<80 then '及格'
else '不及格'
end "语文"
from test t
/**
4.请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，
只是作为一个格式向大家请教。
table1
月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 （result）
部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
create table yj01(
month varchar2(10),
deptno number(10),
yj number(10)
);
insert into yj01(month,deptno,yj) values('一月份',01,10);
insert into yj01(month,deptno,yj) values('二月份',02,10);
insert into yj01(month,deptno,yj) values('二月份',03,5);
insert into yj01(month,deptno,yj) values('三月份',02,8);
insert into yj01(month,deptno,yj) values('三月份',04,9);
insert into yj01(month,deptno,yj) values('三月份',03,8);
create table yjdept(
deptno number(10),
dname varchar2(20)
);
insert into yjdept(deptno,dname) values(01,'国内业务一部');
insert into yjdept(deptno,dname) values(02,'国内业务二部');
insert into yjdept(deptno,dname) values(03,'国内业务三部');
insert into yjdept(deptno,dname) values(04,'国际业务部');
table3 （result）
部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
*/
select distinct de.deptno 部门, yi.yj 一月份, er.yj 二月份, san.yj 三月份
from yjdept de,
(select y.deptno, y.yj
from yj01 y
where y.month = '一月份') yi,
(select y.deptno, y.yj
from yj01 y
where y.month = '二月份') er,
(select y.deptno, y.yj
from yj01 y
where y.month = '三月份') san
where de.deptno = yi.deptno(+)
and de.deptno = er.deptno(+)
and de.deptno = san.deptno(+)
order by de.deptno
-------------------------------------------------
浙公网安备 33010602011771号