동일 컬럼값 안보여주기

1.동일 칼럼값 안보여주기

[1번문제]

원래 테이블:test11

 

먼저 학년별로 가공하기

SELECT COLL
      ,DEPT,DECODE(CNT,1,'1학년',2,'2학년',3,'3학년',4,'4학년') K,
      DECODE(CNT,1,FRE,2,SUP,3,JUN,4,SEN) A
FROM TEST11,(SELECT ROWNUM CNT
            FROM USER_TABLES
            WHERE ROWNUM<5)
            ORDER BY DEPT,K;

  

 

가공된 테이블로 동일 컬럼값 제거 시작!!

(1)
select decode(a.coll,b.coll,null,a.coll) coll
       ,decode(a.coll||A.dept,B.coll||B.dept,null,A.dept) dept
       ,a.grade,a.person
from
    (select row_number() over (order by dept,decode(cnt,1,'1학년',2,'2학년',3,'3학년',4,'4학년'))num
            ,coll,dept
            ,decode(cnt,1,'1학년',2,'2학년',3,'3학년',4,'4학년')grade
            ,decode(cnt,1,fre,2,sup,3,jun,4,sen)person
    from test11,(
                 select rownum cnt
                 from user_tables
                 where rownum<5
     )) a,
    (select row_number() over (order by dept,decode(cnt,1,'1학년',2,'2학년',3,'3학년',4,'4학년'))num
            ,coll,dept
            ,decode(cnt,1,'1학년',2,'2학년',3,'3학년',4,'4학년')grade
            ,decode(cnt,1,fre,2,sup,3,jun,4,sen)person
    from test11,(
                 select rownum cnt
                 from user_tables
                 where rownum<5
     )) b
where a.num-1=b.num(+)
order by a.num
;

 

(2)LAG함수를 이용하여 다시 풀기
select decode(coll,ary_coll,null,coll) coll
      ,decode(coll||dept,ary_coll||ary_dept,null,dept) dept
      ,grade
      ,person
from  
   (
    select row_number() over (order by dept,decode(cnt,1,'1학년',2,'2학년',3,'3학년',4,'4학년'))num
           ,coll,dept
           ,decode(cnt,1,'1학년',2,'2학년',3,'3학년',4,'4학년')grade
           ,decode(cnt,1,fre,2,sup,3,jun,4,sen)person
           ,lag(coll) over(order by coll,dept,decode(cnt,1,'1학년',2,'2학년',3,'3학년',4,'4학년')) ary_coll
           ,lag(dept) over(order by coll,dept,decode(cnt,1,'1학년',2,'2학년',3,'3학년',4,'4학년')) ary_dept
    from test11,(
                 select rownum cnt
                 from user_tables
                 where rownum<5
                 )
    );

 

[2번문제] emp 테이블

원래 테이블보기

(1)
select decode(a.deptno,b.deptno,null,a.deptno) deptno
      ,decode(a.deptno||a.job,b.deptno||b.job,null,a.job) job
      ,a.ename
from (select row_number() over(order by deptno,job) num
             ,deptno,job,ename
     from emp)a
    ,(select row_number() over(order by deptno,job) num
            ,deptno,job,ename
    from emp)b
where a.num-1=b.num(+)
order by a.num;

 

 

(2)LAG함수를 이용하여 다시 풀기
select decode(deptno,dep1,null,deptno) dept
      ,decode(deptno||job,dep1||job1,null,job) job
      ,ename
from (
      select deptno,job
            ,lag(deptno) over(order by deptno,job) dep1
            ,lag(job) over(order by deptno,job) job1
            ,ename
      from emp        
      )
;