oracle中两级联查求和,oracle 级联查询 级联求和 汇总

级联查询

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;