7월29일 - SQL활용 9 : rownum

ROWNUM

 

1. rownum : 행번호매기는 것

   rowid    : 행의 주소값

 

2. 테스트해보기 

 1) 줄번호 

 

select rowid, rownum, hakno, uname from tb_student order by hakno;

 

 2) 행번호 1~3사이 조회 

 

select rownum, hakno, uname from tb_student where rownum>=1 and rownum<=3;

 

 3) 셀프 조인 후 행번호 추가

 

select addr, rownum from (select address as addr       from tb_student       group by address       );

 4) 모조칼럼 rownum 

    -> 모조칼럼으로 쓰는 rownum을 실제칼럼으로 인식시킨 후, 다른 명령어와 병행해서 사용한다. (셀프 조인 후 사용)

 

 

문제) 학번별 수강신청 총학점을 구하고 총학점순으로 정렬 후 위에서부터 3건만 조회 (학번, 이름, 총학점)

 

 - 내 풀이

select rownum rnum, AA.hakno, AA.uname, AA.sumhak from(     select SU.hakno, STU.uname, sum(GW.ghakjum) as sumhak     from tb_sugang SU inner join tb_student STU     on SU.hakno=STU.hakno inner join tb_gwamok GW     on SU.gcode=GW.gcode     group by SU.hakno, STU.uname     order by SU.hakno) AA where rownum>=1 and rownum<=3;

 

 - 선생님 풀이 

1) 과목 코드별 학점 가져오기  select SU.hakno, SU.gcode, GW.ghakjum from tb_sugang SU join tb_gwamok GW on SU.gcode=GW.gcode; 2) 학번별로 총 학점 구하기 select SU.hakno, sum(GW.ghakjum) as 총학점 from tb_sugang SU join tb_gwamok GW on SU.gcode=GW.gcode group by SU.hakno; 3) 총학점순으로 정렬하기 select SU.hakno, sum(GW.ghakjum) as 총학점 from tb_sugang SU join tb_gwamok GW on SU.gcode=GW.gcode group by SU.hakno order by sum(GW.ghakjum) desc 4) 학생이름 가져오고, 행번호 출력하기 select AA.hakno, STU.uname, AA.총학점, rownum from(     select SU.hakno, sum(GW.ghakjum) as 총학점     from tb_sugang SU join tb_gwamok GW     on SU.gcode=GW.gcode     group by SU.hakno     order by sum(GW.ghakjum) desc) AA join tb_student STU on AA.hakno=STU.hakno where rownum>=1 and rownum<=3;

 

 5) 원하는 행번호를 추출하고 싶으면 rownum을 모조칼럼이 아닌 alias값을 주고 실제칼럼으로 만든 후 

    rownum 결과를 추출해낸다 . 

 

select hakno, 총학점, uname, rnum from(     select AA.hakno, STU.uname, AA.총학점, rownum as rnum     from(         select SU.hakno, sum(GW.ghakjum) as 총학점         from tb_sugang SU join tb_gwamok GW         on SU.gcode=GW.gcode         group by SU.hakno         order by sum(GW.ghakjum) desc) AA join tb_student STU     on AA.hakno=STU.hakno) BB where BB.rnum>=4 and BB.rnum<=6;

 

문제) 학번별 수강신청한 총학점을 조회하시오 (단, 수강신청하지 않은 학생의 총학점도 0으로 표시)

 

  - 먼저 학번별 총학점 구한다.

  - 위의 테이블과 수강신청한 학생을 비교해야하므로, tb_student를 left join시켜서 student테이블에 있는 모든 학생의      학번이 출현하게 만든다. 

  - 수강신청을 하지 않은 학생은 null값으로 나오니, nvl()함수를 써서 0으로 표현한다.

 

1) 학번별 총학점 구하기 select tb_sugang.hakno, sum(tb_gwamok.ghakjum) as 총학점 from tb_sugang inner join tb_gwamok on tb_sugang.gcode=tb_gwamok.gcode group by tb_sugang.hakno; 2) 학번별 수강신청한 총학점 구하기 select STU.hakno, nvl(AA.총학점,0) from tb_student STU left join (                                 select tb_sugang.gcode, sum(tb_gwamok.ghakjum) as 총학점, tb_sugang.hakno                                 from tb_sugang inner join tb_gwamok                                 on tb_sugang.gcode=tb_gwamok.gcode                                 group by tb_sugang.gcode, tb_sugang.hakno                                ) AA on STU.hakno=AA.hakno order by hakno;

 

 

문제) 학생테이블에서 학번순으로 정렬 후 행번호를 아래와 같이 붙여서 조회하시오 
   -- 8 g1001 / 7 g1002 / 6 g1003 ... 행번호 거꾸로 출력

 

  - 이 문제는 학번과 행번호를 반대로 교차시키도록 생각하면 된다.

  - 학번을 먼저 거꾸로 조회한 후 한 테이블로 묶고, 행번호를 따로 조인해주어 desc해주면 학번은 내림차순으로,

    행번호는 오름차순으로 정렬된다.

 

1) 학번을 내림차순 조회  select hakno, uname from tb_student  order by hakno desc; 2) 행번호 추가 select hakno, uname, rownum rnum from(     select hakno, uname     from tb_student      order by hakno desc     ); 3) 행번호 내림차순 정렬 select rownum rnum, hakno, uname from(     select hakno, uname     from tb_student      order by hakno desc     ) order by rnum desc;

 

 4) rownum 모조칼럼을 실제칼럼으로 추가

  -> 3번으로도 맞는 답안이지만, rownum을 좀 더 확실히 실제칼럼으로 만들어주어서 설계를 해본다 .

 

select rnum, hakno, uname from(     select hakno, uname, rownum rnum     from(         select hakno, uname         from tb_student          order by hakno desc         )     ) order by rnum desc;