Original Query
select empno, deptno, to_char(hiredate,'yyyy') hire_yr, sal,
sum(sal) keep (dense_rank first order by to_char(hiredate,'yyyy') ) over (partition by deptno) avg_sal_yr1_hire
from scott.emp
where deptno in (20, 10)
Simulated Query
select empno, deptno, to_char(hiredate,'yyyy') hire_yr, sal,
(select sum(sal) from scott.emp a where a.deptno = b.deptno and a.to_char(hiredate,'yyyy') = (select to_char(min(hiredate),'yyyy') from scott.emp c where a.deptno=c.deptno)) avg_sal_yr1_hire
from scott.emp b
where deptno in (20, 10)
order by deptno, empno, hire_yr;
select empno, deptno, to_char(hiredate,'yyyy') hire_yr, sal,
sum(sal) keep (dense_rank first order by to_char(hiredate,'yyyy') ) over (partition by deptno) avg_sal_yr1_hire
from scott.emp
where deptno in (20, 10)
order by deptno, empno, hire_yr;
Simulated Query
select empno, deptno, to_char(hiredate,'yyyy') hire_yr, sal,
(select sum(sal) from scott.emp a where a.deptno = b.deptno and a.to_char(hiredate,'yyyy') = (select to_char(min(hiredate),'yyyy') from scott.emp c where a.deptno=c.deptno)) avg_sal_yr1_hire
from scott.emp b
where deptno in (20, 10)
order by deptno, empno, hire_yr;
No comments:
Post a Comment