Oracle递归查询与常⽤分析函数
最近学习oracle的⼀些知识，发现⾃⼰sql还是很薄弱，需要继续学习，现在总结⼀下哈。
（1）oracle递归查询 start with ... connect by prior ，⾄于是否向上查询（根节点）还是向下查询（叶节点），主要看prior后⾯跟的字段是否是⽗ID。
向上查询：select * from test_tree_demo start with id=1 connect by prior pid=id
查询结果：
向下查询：select * from test_tree_demo start with id=3 connect by prior id=pid
如果要进⾏过滤，where条件不能放在connect by 后⾯，如下：select * from test_tree_demo where id !=4 start with id=1 connect by prior pid=id
（2）分析函数- over（ partition by ）
数据库中的数据如下：select * from testemp1
select deptno,ename,sal,sum(sal)over() deptsum from testemp1 如果over中不加任何条件，就相当于sum（sal），显⽰结果如下：
merge函数 ⼀般over都是配合partition by order by ⼀起使⽤，partition by 就是分组的意思。下⾯看个例⼦：按部门分组，同个部门根据姓名进⾏⼯资累计求和。
select deptno,ename,sal,sum(sal)over(partition by deptno order by ename) deptsum from testemp1，显⽰如下：
其实统计各个部门的薪⽔总和，可以使⽤group by实现，select deptno,sum(sal) deptsum from testemp1 group by deptno，结果如下：
但是，使⽤group by 的时候查询出来的字段必须是分组的字段或者聚合函数。例如查询结果多加个ename字段。使⽤partition by 就简单了。
select deptno,ename,sum(sal) over (partition by deptno) from testemp1，显⽰如下：
（3）分析函数-rank（），dense_rank（），row_number（）
select deptno,ename,sal,rank() over(partition by deptno order by sal) from testemp1，结果如下:
select deptno,ename,sal,dense_rank() over(partition by deptno order by sal) from testemp1，结果如下：
可以看出使⽤rank()和dense_rank()的区别了吧。接下来在看看row_number()
select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) from testemp1，显⽰结果如下:
（4）分析函数-group by rollup
根据部门分组，统计各个部门各个职位的薪⽔总和。
select group_id,job,sum(salary) as salary from group_test group by rollup (group_id,job)，显⽰结果如下：
group by rollup （a,b,c）相当于group by (a,b,c) union group by(a,b) union group by (a) union 全表。
上述结果可以⽤group by 与 union实现，如下：order by 1 ,2 就是根据第⼀⼆列进⾏排序
select group_id, job, sum(salary) from group_test group by group_id, job
union all
select group_id, null, sum(salary) from group_test group by group_id
union all
select null, null, sum(salary) from group_test
order by 1, 2;
可以结合grouping（）函数⼀起使⽤，如下：
select group_id,case when grouping(group_id) = 0 and grouping(job) = 1 then '⼩计'
when grouping(group_id) = 1 and grouping(job) = 1 then '总计'
else job end as job,
sum(salary) as salary
from group_test
group by rollup(group_id, job);
显⽰如下：当grouping()为空的时候返回1，⾮空返回0.
（5）分析函数-group by cube
group by cube(a,b)=group by(a,b) union group by (a) union group by (b) union (全表)
select group_id,job,sum(salary) as salary from group_test group by cube (group_id,job)，显⽰如下：
上述结果可以⽤group by 与 union实现，如下：
select group_id, job, sum(salary) from group_test group by group_id, job
union all
select group_id, null, sum(salary) from group_test group by group_id
union all
select null, job, sum(salary) from group_test group by job
union all
select null, null, sum(salary) from group_test
order by 1, 2;
（5）merge into
最近接触到oracle这个函数，感觉挺好的。假如我们现在有两个表A，B，其中有部分数据是A ,B表⼀样的，有⼀部分数据是B 有的，⽽A表没有的，现在有⼀个需求，将两个表整合在⼀个表中。那么按照之前，我们⼀般都是根据A表某个唯⼀的字段查询B表，如果存在，则跳过，不存在则插⼊到A表。要实现这个需求，我们需要两步才能实现，如果使⽤merge into 则⽅便很多了。
merge into的结构如下：
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col_val1,
col2 = col_val2
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
下⾯我们看⼀个简单的例⼦： A表：group_test B表：testemp1
A表的数据如下： B表的数据如下：
现在将B中deptno为50的数据插⼊到A表，如下：
merge into group_test t1
using (select * from testemp1 where deptno = 50) t2
on (t1.group_id = t2.deptno)
when matched then
update set t1.salary = 1000
when not matched then
insert (group_id, job,name,salary) values (t2.ame,'gdpuzxs',5000)
显⽰如下：因为A表中没有group_id=50，所以执⾏插⼊。
接着，我们在执⾏⼀下上⾯那个语句，
显⽰如下：因为A表中存在group_id=50，所以执⾏更新操作。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论