Oracle Sql优化之报表和数据仓库运算

select job,
          sum(case deptno when  then sal end) as sal10,
          sum(case deptno when  then sal end) as sal20,
          sum(case deptno when  then sal end) as sal30,
          sum(sal) as allsal
from emp
group by job
order by ;

select *  
from (select job,sal,deptno from emp)
pivot(sum(sal) as s for deptno in( as d10,, as d30))
order by ;

select ‘’ as deptno, deptno_10_ct as rc from test
union all
select '' as deptno, deptno_20_ct as rc from test

select * from test
unpivot (rc for deptno in(deptno_10_ct,deptno_20_ct));

select deptno,rc.sal
from test
unpivot include null(rc for deptno in(deptno_10_ct as ,deptno_20_ct as ))
unpivot include null(sal for deptno1 in(deptno_10_s as ,deptno_20_s as ))
where deptno =deptno1;

select case when lag(job) over(order by job,ename)=job then null else job end as fjob,ename as fename
from emp1
where deptno=
order by job,ename

select ntile() over(order by empno) as g,empno,ename,job
from emp
where job in ('MANAGER','CLERK');

select deptno,job,sum(sal)
from emp
group by rollup((deptno,job));

select case grouping(deptno) when  then '总计'  else to_char(deptno) end as code,
          case when grouping(deptno)= then null when grouping(job)= then '小计' else job end as gz,
          case when grouping(job)= then null when grouping(mgr)= then '小小计' else to_char(mgr) end as mmgr,
          max(case when empno in(,,) then empno end) as max_empno,
          sum(sal) as salsum
from emp1
group by rollup((deptno,job,mgr));

select case grouping(deptno)|| grouping(job)
          when '' then 'gdepjob'
          when '' then 'gdep'
          when '' then 'gjob'
          when '' then 'gall' end as gelem,
         deptno,job,sum(sal) as allsal
from emp
group by cube(deptno,job)
order by grouping(job) desc,grouping(deptno)desc, deptno asc;

select ename,sal,
          sum(sal) over(order by sal) as sal1,
          sum(sal) over(order by sal range between unbounded preceding and current row) as sal2,
          sum(sal) over(order by sal rows between unbounded preceding and current row) as sal3,
          sum(sal) over() as sal4,
          sum(sal) over(order by sal range between unbounded preceding and unbounded following) as sal5,
          sum(sal) over(order by sal rows between unbounded preceding and unbounded following) as sal6
from emp
where deptno =;

with l as
(select level as lv from dual connect by level<=),
m as 
(select a.lv as alv,b.lv as blv,
           to_char(b.lv)|| 'x'|| to_char(a.lv)||' = '|| rpad(to_char(a.lv*b.lv),,' ') as text
  from l a,l b
  where b.lv<=a.lv)
select listagg(m.text,'  ') within GROUP(order by m.blv) as 小九九
from m
group by m.alv;