[DataBase-공부] 9. 행 복제, 열 복제를 해보자

-----------------------------

--conn n1/n1

-----------------------------

--cartesian join, cross join

--원래는3번의 쿼리문을 날려야 하는데 이렇게 rownum 갯수과 조인을 하게되면

--한번에 읽어들여서 원하는 형태로 조작가능(부서별, 업무별로 나누고 부서별 합계를 낼수 있음)

--합계

select decode(rnum,1,department_id,2,department_id) as 부서

      ,decode(rnum,1, job_id)

      ,sum(sumsal)

from(select department_id, job_id, sum(salary) as sumsal

from employees

group by department_id, job_id

order by 1,2), (select rownum as rnum from employees where rownum <= 3)

group by decode(rnum,1,department_id,2,department_id), decode(rnum,1,job_id)

order by 1,2;

select nvl(decode(rnum,1,to_char(department_id),2,to_char(department_id)),to_char('전체')) as 부서

      ,nvl(decode(rnum,1, job_id),'합계') as 업무

      ,sum(sumsal)

from(select department_id, job_id, sum(salary) as sumsal

from employees

group by department_id, job_id

order by 1,2), (select rownum as rnum from employees where rownum <= 3)

group by nvl(decode(rnum,1,to_char(department_id),2,to_char(department_id)),to_char('전체')), decode(rnum,1,job_id)

order by 1,2;

--열복제 후 표로 출력

select job

      ,sum(decode(deptno,10,sal)) as d10

      ,sum(decode(deptno,20,sal)) as d20

      ,sum(decode(deptno,30,sal)) as d30

      ,sum(decode(deptno,40,sal)) as d40

      ,sum(decode(nvl(deptno,99),99,sal)) as d99

      ,sum(sal) as "Sum"

from emp

group by job;

----------------------------------

--n1/n1

----------------------------------

select * from departments;

select job_id

      ,sum(decode(department_id,10,salary)) as d10

      ,sum(decode(department_id,20,salary)) as d20

      ,sum(decode(department_id,50,salary)) as d50

      ,sum(decode(department_id,60,salary)) as d60

      ,sum(decode(department_id,80,salary)) as d80

      ,sum(decode(department_id,90,salary)) as d90

      ,sum(decode(department_id,110,salary)) as d110

      ,sum(decode(department_id,190,salary)) as d190

      ,sum(decode(nvl(department_id,999),999,salary)) as d999

      ,sum(salary) as "합계"

from employees

group by job_id;

표 응용)

------------------------

--scott/tiger--

------------------------

--세로 부서,가로 업무, 사람 수

select distinct job from emp;

 

select job

      ,count(decode(deptno,10,1)) as d10

      ,count(decode(deptno,20,1)) as d20

      ,count(decode(deptno,30,1)) as d30

      ,count(decode(deptno,40,1)) as d40

      ,count(decode(nvl(deptno,99),99,1)) as d99

      ,count(1) as "합"

 

from emp

group by job;

select nvl(deptno,'999') as 부서

      ,count(decode(job,'CLERK',1))as "업무1"

      ,count(decode(job,'SALESMAN',1)) as "업무2"

      ,count(decode(job,'PRESIDENT',1)) as "업무3"

      ,count(decode(job,'MANAGER',1)) as "업무4"

      ,count(decode(job,'ANALYST',1)) as "업무5"

      ,count(decode(nvl(job,'None'),'None',1)) as "none"

      ,count(1) as "합계"

            

from emp

group by deptno

order by 1;