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;