39.oracle高级篇

　　标题说是高级篇，其实也就是相对于基础篇来说的，也不是很深奥，自己平时工作中也都会用到，这里回忆的并不是特别冷门的知识，不要掉以轻心，以为“高级”就觉得工作中不会用到了。

一、select into 和 insert into select 两种表复制语句

create table <new table> as select * from <exists table>，要求目标表不存在。

insert into table2(f1,f2,...) select v1,v2,.... from table1。要求目标表table2必须存在，由于目标表已经存在，所以除了插入源表table1的字段外，还可以插入常量。

 

二、merge into

merge into table1

using table2

on table1和table2进行关联

　　when matched then

　　　　update set...

　　when not matched then

　　　　insert(....)values....

例如：

merge into products p using (select * from newproducts) np on (p.product_id = np.product_id)
when matched then
update set p.product_name = np.product_name where np.product_name like 'OL%'
when not matched then
insert values(np.product_id, np.product_name, np.category) where np.product_name like 'OL%'

 

三、分层查询（查出一颗树）

select * from emp
start with empno=7369
connect by prior mgr=empno

pid在前表示向上（根）查询，pid在后表示向下查询

 

四、分析函数（统计用）

4.1 voer

select empno,ename,job,sal,
  (sum(sal) over(order by empno)) orderSum ,--连续求和
  sum(sal) over() total,--总和
  100*round(sal/sum(sal) over(),5) perc --份额
from emp
from emp

4.2 partition 部门连续求和

select empno,ename,deptno,sal,
       sum(sal) over(partition by deptno order by empno ) dept_Order_Sum,--部门连续求和
       sum(sal) over(partition by deptno) dept_Sum--部门求和
from emp

4.3 组内排名

select empno,deptno,sal,
　　row_number() over(partition by deptno order by sal desc) as "order"
from emp

 

4.4 rollup 在group by 的基础上再次分组（月份的小计）

select
　　to_char(hiredate,'YY') HIREDATE,JOB,SUM(SAL) "SUM" 　　
from emp group by  ROLLUP(to_char(hiredate,'YY'),JOB)

 

4.5 cube函数（一直不太理解。。。）

 两次分组，比如先按照月份分组，再按照区域分组，统计出工资多少。

4.6 rank函数和dense_rank函数

应用场景：排名

select
       to_char(emp.hiredate,'YY') "year",
       job,
       emp.sal,
       rank() over(partition by to_char(emp.hiredate,'YY'),job order by sal desc) "order"
from emp

 

假设 81年组 的 salesman 组 后面最后还有一行的sal为1240，那么排名应该是4，也就是1240这一行前面实际上是已经有4行了，但1240还是还在了第四。如果希望这一排名是5的话，那么应该用dense_rank。

4.7 row_number()函数

select
       to_char(emp.hiredate,'YY') "year",
       job,
       emp.sal,
       row_number() over(partition by to_char(emp.hiredate,'YY'),job order by sal desc) "order"
from emp