[SQL] rownum

[rownum]

⊙ rownum : 테이블의 행번호 (시퀀스처럼 보이지만 시퀀스랑은 상관없음, 보고 있는 테이블에 행번호를 붙여주는 것) 

                    : 시퀀스처럼 중간에 삭제되면 번호가 붕 뜨는 것과 같은 일이 없음

                    : 페이지 작업할 때 많이 사용

⊙ rowid    : 행의 주소값

 

1) 줄번호 

select rowid, rownum, hakno, uname  from tb_student;

 

 

 

 

 

2) 정렬 후 줄번호 삽입

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

 

 

3) where절을 사용하여 줄번호 1~3 사이 조회

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

 

 

 

4) where절을 사용하면 줄번호 4~6 사이 조회 (데이터가 있지만 조회가 안 됨)

select rownum, hakno, uname  from tb_student  where rownum>=4 and rownum<=6;

 → rownum을 사용하는 방식이 존재함을 확인할 수 있음

 

 

+) ORA-00979: not a GROUP BY expression 00979. 00000 -  "not a GROUP BY expression"
▶ 줄번호(rownum) 를 넣고 group by 수행시 에러 발생

select address, rownum
from tb_student
group by address;

→ group by에 그냥 rownum을 넣을 경우 에러가 발생하기 때문에 셀프 조인 후 행번호 추가

    

 

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

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

 

 

6) 줄번호 4~6 조회

① 셀프 조인 전

select hakno, uname, address  from tb_student  order by hakno;

② 셀프 조인

select rownum runm, hakno, uname, address  from (            select hakno, uname, address            from tb_student            order by hakno          );

 

⊙ rownum 은 모조칼럼이지만 실제로 있는 것처럼 사용할 수 있음
⊙ 그러나 where 등과 같은 조건절에 함께 쓰이려면 완벽히 컬럼같이 인식될 수 있어야 하기 때문에 셀프 조인을 이용해서 rnum 와 같이 컬럼명 변경하여 실제 컬럼으로 인식하게 한 후 사용할 것을 추천 
(ex. '행번호가 4~6을 조회하시오' : 모조칼럼 rownum을 그대로 사용하면 결과값이 안 나오기도 함)

 

7) 모조칼럼 rownum을 실제 컬럼으로 인식시킨 후 다른 명령어와 병행해서 사용한다 (셀프조인 후 사용할 것)

① rownum의 셀프 조인 전 from 에 들어갈 쿼리문

select hakno, uname, address  from tb_student   order by hakno;

② 셀프 조인

select rnum, hakno, uname, address  from (            select rownum rnum, hakno, uname, address            from (                       select hakno, uname, address                       from tb_student                        order by hakno                    )         )  where rnum>=4 and rnum<=6;

 

 

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

   (+ 해당하는 조건이 적어서 자료 추가함)

 

 <추가된 자료>

 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1005', 'p002');
 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1005', 'c011');
 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1002', 'd002');

 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1003', 'd002');
 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1003', 'p001');

 

select hakno, uname, sum 총학점                                      --학번, 이름, 총학점을 구하라고 함  from(           select rownum rnum, AA.hakno, STU.uname, sum         --셀프조인으로 rownum을 만들 수 있는 환경을 만들고           from (                                                                    hakno의 경우 AA와 STU에 동시에 존재하기 때문에 특정                      select SU.hakno, sum(GW.ghakjum) as sum      --학번별 수강신청 총학점을 구함                       from tb_sugang SU join tb_gwamok GW                       on SU.gcode=GW.gcode                       group by SU.hakno                  ) AA join tb_student STU                                  --학생 이름을 구해야 하기 때문에 STU로 조인             on AA.hakno=STU.hakno              )  where rnum>=1 and rnum<=3                                          --위에서부터 3건만 조회하라고 했기 때문에   order by sum desc;                                                            --총학점순으로 정렬    → 굳이 rownum을 rnum 이라고 실제 컬럼을 만들어 주는 순서를 하지 않았어도 되지만 조건절에서 모조컬럼으로 있을 때 인식되지 않을 수도 있기 때문에 바꿔줌

 

 <진행 순서>

  

①  과목 코드별 학점가져오기

select SU.hakno, SU.gcode, GW.ghakjum  from tb_sugang SU join tb_gwamok GW  on SU.gcode=GW.gcode;

 

 

 

 

 

   

② 학번별로 총학점 구하기

select SU.hakno, sum(GW.ghakjum) as 총학점  from tb_sugang SU join tb_gwamok GW  on SU.gcode=GW.gcode  group by hakno;

   

③ 총학점순으로 내림차순 정렬하기

select SU.hakno, sum(GW.ghakjum) as 총학점  from tb_sugang SU join tb_gwamok GW  on SU.gcode=GW.gcode  group by hakno  order by sum(GW.ghakjum) desc;

 

 

④ 학생명이 나올 수 있게 학생테이블 조인

select AA.hakno, STU.uname, 총학점, rownum  from(           select SU.hakno, sum(GW.ghakjum) as 총학점           from tb_sugang SU join tb_gwamok GW           on SU.gcode=GW.gcode           group by hakno           order by sum(GW.ghakjum) desc        ) AA join tb_student STU on AA.hakno=STU.hakno;

  

⑤  위에서부터 3건만 볼 수 있게 하는 조건 만들기  (+ 해당하는 조건이 적어서 자료 추가함)

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

 

 

 

문2) 행번호 4~6 출력하기

검색이 되지 않는 경우>   - 중간값은 모조칼럼 rownum 을 사용했기 때문에 잘 나오지 않을 수 있음, 모조칼럼을 조건절에 직접 사용하지 말 것    → 해결 방법 : 모조칼럼 rownum에 alias 값을 주어 실제칼럼으로 인식 후 사용하게 함

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

방법1>  +) 방법2. 첫 번째 from을 alias 를 지정해서 BB 로 설정 후 where 절을 쓰는 방법

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

 

문3) 학번별 수강신청한 총학점을 조회하시오 (단, 수강신청하지 않은 학생의 총학점도 0으로 표시)
 예시) g1001 홍길동 8
           g1002 무궁화 6
           g1003 진달래 0

방법1>

select AA.hakno, STU.uname,  sum 총학점  from (  select STU.hakno, sum(nvl(GW.ghakjum, 0)) sum  from tb_student STU left join tb_sugang SU  on STU.hakno=SU.hakno left join tb_gwamok GW  on SU.gcode=GW.gcode  group by STU.hakno  ) AA join tb_student STU  on AA.hakno=STU.hakno  order by AA.hakno;

 

<방법2>  

 

 

① 수강 학생들의 총학점

select tb_sugang.gcode, sum(tb_gwamok.ghakjum) as 총학점  from tb_sugang inner join tb_gwamok  on tb_sugang.gcode=tb_gwamok.gcode  group by tb_sugang.gcode;

 

② 학번, 총학점 (null값으로 나옴), 총학점 (null → 0)  →  학점을 얻지 않은 학생은 총학점이 null로 표시가 되지만       nvl 을 이용하여 null이 나올 때 0으로 바꿔줌

select STU.hakno, AA.총학점, nvl(AA.총학점, 0) from tb_student STU left join (select tb_sugang.gcode, sum(tb_gwamok.ghakjum) as 총학점  from tb_sugang inner join tb_gwamok  on tb_sugang.gcode=tb_gwamok.gcode  group by tb_sugang.gcode)

 

      
 
 문4) 학생테이블에서 행번호를 아래와 같이 붙여서 조회하시오 (학번순 정렬)
  8 g1001 홍길동 hong1@naver.com  서울 111-5555
  7 g1002 홍길동 hong2@google.com 제주 222-5555
  6 g1003 개나리 user3@daum.net   서울 333-5555
  .
  .

 

방법1>

select rownum, AA.*  from(            select STU.*            from tb_student STU            order by STU.hakno desc          ) AA  order by rownum desc;

 

<진행방법/방법2>

 

① 학번을 내림차순 조회

select hakno, uname  from tb_student   order by hakno desc;

 

② 행번호

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

 

③ rownum을 기준으로 내림차순 정렬 + rownum 을 실제칼럼으로 추가

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