oracle：多表关联 join on，单行子查询，多行子查询，TOP-N，行转列

/*多于两张表的关联
如果有多个表参与查询，先把t1xt2笛卡尔积得到一个大表T1，再把T1xt3笛卡尔积得到一个另外的大表T2，依次类推。
所有的多表查询最终都是两种表的查询。
*/

-- 查询SMITH管理者的薪资等级
select e.ename,m.ename,s.grade
from emp e,emp m,salgrade s
where e.mgr=m.empno and (m.sal between s.losal and s.hisal)and e.ename='SMITH'

------------------多表关联(A) - 99 语法
/*92的问题:
[1]表的过滤条件和表的连接条件混合在一起，维护麻烦
[2]数据库的数据适合变化，根据where子句的执行规则，sql语言也会相应发生变化，给维护造成一定成本。
语法：
-- 99 笛卡尔积
select *
from dept d cross join emp e

*/

----------------自然连接
/*
NATURAL JOIN子句基于两个表中列名完全相同的列产生连接

[1]两个表有相同名字的列
[2]数据类型相同
[3]从两个表中选出连接列的值相等的所有行
注意:
自然连接最优的使用场景是：主外键关系且主外键字段只有一个。
*/

-- 自然连接(自然连接只保留一个重复列)
select *
from dept d natural join emp e


-----------using
/*
using 主要用于指定连接字段。
[1] 按照指定的字段连接两个表。
[2] 选指定字段值相同的数据行。

using 同样适用于自然连接。
*/



---------------------------on 指定连接条件
/*自然连接的条件是基于表中所有同名列的等值连接
为了设置任意的连接条件或者指定连接的列，需要使用ON子句
连个表的关联用关键字 join ，默认内连接(inner) 语法
select filed1,fild2,…
from table1 
join table2 on condition1
     [join table3 on condition2]*
     */
-- 查询出员工的部门名称
select *
from dept d inner join emp e
on d.deptno=e.deptno


-- 查询SMITH的上级领导
select e.ename,m.ename
from emp e inner join emp m
on e.mgr=m.empno
where e.ename='SMITH'

--使用join on语句连接3张表
--
-- 查询SMITH的管理者名称和其管理者的薪资等级

select e.ename,m.ename,s.grade
from emp e inner join emp m on e.mgr=m.empno
inner join salgrade s on m.sal between s.losal and s.hisal
where e.ename='SMITH'


--99外连接
/*外连接在99语法中通过outer 关键字，按照主从表的位置可以分为left outer/right outer,
语法
select filed1,field2
from table1 left/right outer join table2 on condition1
      [left/right outer join table3 on condition2]*
*/
-------------左外连接
--------------查询所有部门的所有员工
select *
from dept d left outer join emp e on d.deptno = e.deptno

---右外连接
select * 
from emp e right outer join dept d on e.deptno = d.deptno


--------------------------------子查询
---sql中查询是可以嵌套的。一个查询可以作为另外一个查询的条件、表。
/*
SELECT    select_list
FROM    table
WHERE    expr operator
        (SELECT    select_list
             FROM    table);
       
 解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。子查询可以作为一个虚表被使用。

子查询要用括号括起来 
将子查询放在比较运算符的右边(增强可读性)

子查询根据其返回结果可以分为单行子查询和多行子查询。      
       
*/

----------------单行子查询

--当子查询有单行时，可以取单行中的一个字段形成单个值用于条件比较。

-- 查询雇员其薪资在雇员平均薪资以上
-- [1] 查询员工的平均薪资
select avg(e.sal) "AVGSAL"
from emp e

--[2] 查询满足条件的雇员
select *
from emp e1
where e1.sal>(select avg(e.sal) "AVGSAL" from emp e)


---------------------多行子查询

-- 查在雇员中有哪些人是管理者
--【1】查询管理者
select  distinct e.mgr
from emp e
where e.mgr is not null

--【2】查询指定列表的信息 in
select e.*
from emp e
where e.empno in (select  distinct e.mgr
from emp e
where e.mgr is not null)

-- 部门编号为20的所有员工中收入最高的职员
-- [1]查询部门为20的雇员的所有工资
select e.sal from emp e where e.deptno=20

--[2] 部门编号为20的所有员工中收入最高的职员
select e.*
from emp e
where e.sal>=all(select e.sal from emp e where e.deptno=20) and e.deptno=20


-----多行子查询返回的结果可以作为 表 使用，通常结合in、some/any、all、exists。

-------------From后的子查询
---子查询可以作为一张续表用于from后。

-- 每个部门平均薪水的等级
--【1】部门的平均薪资
select e.deptno,avg(e.sal) "AVGSAL"
from emp e
group by e.deptno

--【2】求等级
select vt.deptno,vt.avgsal,s.grade
from (select e.deptno,avg(e.sal) "AVGSAL"
from emp e
group by e.deptno
) VT,salgrade s
where vt.avgsal between s.losal and s.hisal



--求平均薪水 最高的部门的 部门编号
select vt.deptno,vt.avgsal
from(select e1.deptno,avg(e1.sal) "AVGSAL"
from emp e1
group by e1.deptno) VT
where vt.avgsal=(select max(vt.avgsal) from(select avg(e.sal) "AVGSAL"
from emp e
group by e.deptno) VT)

-------------TOP-N
/*把select得到的数据集提取前n条数。
rownum:表示对查询的数据集记录的编号，从1开始。*/
-- 查询前10名雇员
select e.*,rownum
from emp e
where rownum<=10
---------- 查询按照薪资降序，前10名雇员
select vt.*
from (select e.*
from emp e
order by e.sal desc) VT
where rownum <=10
/*
总结
[1] order by 一定在整个结果集出现后才执行。
[2] rownum 在结果集出现后才有编号。
。。。-> select -> rownum -> order by
*/
-------------------分页
/*求page=n,pagesize=size的数据
=>[(n-1)*size+1,n*size]

select vt0.*
from (select t.*, rownum “RN”
from table t 
where rownum <= n*size) VT0
where vt0.rn >= (n-1)*size+1
*/

-- -- 查询6-10号的雇员

select vt.*
from (select e.*,rownum "RN" from emp e where  rownum<=10) VT
where vt.rn>=6



----------------------行转列


create table test_score
(
  name    VARCHAR2(20),
  subject VARCHAR2(20),
  score   NUMBER(4,1)
)

insert into test_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into test_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into test_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into test_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into test_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into test_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into test_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into test_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into test_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);


select * from test_score ;


-- 行转列问题
select t.name,
sum(decode(t.subject,'语文',t.score)) "语文",
sum(decode(t.subject,'数学',t.score)) "数学",
sum(decode(t.subject,'英语',t.score)) "英语"
from test_score t
group by t.name