oracle 级联查询 级联求和 汇总

fvepacyizq

级联查询

select level||'层',lpad('*',level*5)||id id ,connect_by_isleaf
from test
start with superid = '0' connect by prior id=superid;

/*------method one------*/
select superid,ltrim(max(sys_connect_by_path(id,';')),';') from(
select superid,id,row_number() over(partition by superid order by superid) id1,
row_number() over(order by superid) + dense_rank() over(order by superid) id2 from test )
start with id1=1 connect by prior id2 = id2 -1
group by superid order by superid;
/*------method two------*/
select distinct superid,orig_id,ltrim(first_value(id) over(partition by superid order by l desc),';')
from(
select superid,id orig_id,rn,parent_rn,level l,sys_connect_by_path(id,';') id
from (select superid,id,superid||to_char(rownum-1) rn,superid||rownum parent_rn from test)
connect by prior parent_rn = rn )
order by orig_id;

===============
--一级汇总 二级汇总
--cube 双向 rollup单向汇总
--group by grouping sets 等于多次使用group by
select manager,location_name,sum(sales) from test_sales
--group by rollup(manager,location_name)
group by grouping sets(manager,location_name);
drop table test_sales
--级联求和
select deptno,ename,sal,sum(sal) over(order by ename),
sum(sal) over(),
100*round(sal/sum(sal) over(),4)
from emp
where deptno=20
select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) 分部门连续和,
sum(sal) over(partition by deptno) 部门总和,
sum(sal) over(order by deptno,ename) 部门总和
--order by 连续求和顺序 where就不用order by了
from emp;