본문 바로가기

IT기반지식/데이터베이스

데이터베이스별 결과 제한 쿼리(top, rownum, Limit, inst_num, groupby_num, orderby_num)

1. MSSQL - TOP키워드
   너무 잘 알고 있듯이 다음과 같이 쓰면 된다.
   SELECT top 100 * FROM testTable

2. Oracle - rownum 키워드
ROWNUM 기본

  SELECT id,password, rownum FROM test WHERE rownum<=5 order by idx desc


 최근 사용한 DDL 구문을 10개까지만 보기 위해 날짜를 내림차순 정렬해서
ROWNUM <= 10 까지만 출력했지만 제대로 된 결과가 출력되지 않았다.

    select anydata, anydate, rownum 
    from anytable 
    where rownum <=10
    order by anydate desc;

rownum 번호는 where절에서 매겨지고, 그 이후에 정렬이 되기 때문인데, 결론적으로 정렬부터 하고 rownum 번호를 부여하면 된다.

    select anyalias.anydata, anyalias.anydate, rownum
    from (select anydata, anydate
            from anytable
            orderby anydate desc) anyalias
    where rownum <= 10;

이 상태에서 5번째에서 10번째까지의 행을 보고 싶다면 mysql 에서는 limit 4,6 를 하면 될것이다.

그러나 오라클에서 

    select anyalias.anydata, anyalias.anydate, rownum
    from (select anydata, anydate
            from anytable
            orderby anydate desc) anyalias
    where rownum >= 5 and rownum =<10;

라는 쿼리를 실행하면 어떤 행도 반환되지 않을 것이다.

이유에 대해서는 http://www.oracle.com/technology/global/kr/oramag/oracle/06-sep/o56asktom.html

페이지에서 톰아저씨가 설명을 해주신다.

ROWNUM은 조건자 실행 과정에서 각 로우에 할당되며, 로우가 WHERE 조건에 의해 처리된 이후에만 증가된다.

즉, WHERE 절의 조건에 ROWNUM을 포함시켰을 경우, 처음 할당되는 값 1이 조건문을 만족(TRUE)시키지 않는다면 이후에 비교되는 행들에 대해서도 ROWNUM은 증가하지 않고 1인 상태인 것이다.

이번에는 정렬한 다음 ROWNUM까지 할당하고 조건을 부여한다.

    select *
    from (select anyalias.anydata, rownum rownum_alias 
             from (select anydata 
                      from anytable order by anydata) anyalias       -- 정렬
             where rownum =< 10)                                            -- rownum 10번까지 할당, 
    where rownum_alias >= 5;                                               -- 할당받은 rownum에 대해 조건 부여

rownum을 할당 받을 때 쓰는 조건을 무조건 참인 조건으로 줘도 상관 없다.

    select *
    from (select anyalias.anydata, rownum rownum_alias 
             from (select anydata 
                      from anytable order by anydata) anyalias
             where 1=1)
    where rownum_alias between 5 and 10;

 mysql> select * from testTable where style='angle' limit 10;

>> 한도 10개까지만 가져온다.

 mysql> select * from userTable where sex='male' limit 0, 20;

>> 이경우 0부터 시작한다. 첫번째부터 20개까지만 추출한다.

 mysql> select no from userTable where gender='male' limit 10, 20;

>> 11 번째 부터 20개까지만 추출한다. (0부터 시작이니까 0~9, 10~19, 20~29,... )


4. 큐브리드(or UniSQL)
 결과를 필터링하는 용도로, 그것이 적용되는 위치에 따라서 다음의 세 가지 함수를 지원한다.
현재의 상용 DBMS 중에서는 가장 다양한 옵션을 제공
inst_num() - rownum 으로도 표현됨
groupby_num()
orderby_num()

1. inst_num()은 rownum 으로도 표현되며, WHERE 절의 검색 조건을 만족하는 결과 튜플에 적용하여, 이를 필터링
아래와 같이, WHERE 절의 검색 조건에 inst_num() 검색 조건을 추가하여 기술
sqlx> select inst_num(), a, b from tbl where a > 0 and inst_num() between 2 and 3;
sqlx> ;run

=== <Result of SELECT Command in Line 1> ===

inst_num() a b
=======================================
2 2 4
3 3 6

2. groupby_num()은 GROUP BY 절이 있는 경우에, WHERE 절을 통과한 결과 튜플을 그룹핑한 결과에 적용하여, 이를 필터링, 예를 들어, 그룹핑한 결과의 두번째, 세번째 튜플을 가져오려면 HAVING 절에 검색조건을 추가하여 기술
sqlx> select groupby_num(), a, count(*) from tbl group by a having groupby_num() between 2 and 3;
sqlx> ;run

=== <Result of SELECT Command in Line 1> ===

groupby_num() a count(*)
=========================================
2 1 1
3 2 1

3. orderby_num()은 ORDER BY 절의 순서대로 정렬한 최종 결과튜플에 적용하여, 이를 필터링
예를 들어, 최종 결과의 두번째, 세번째 튜플을 가져오려면 ORDER BY ... FOR .. 에 검색 조건을 기술
sqlx> select orderby_num(), a, b from tbl order by a for orderby_num() between 2 and 3
sqlx> ;run

=== <Result of SELECT Command in Line 1> ===

orderby_num() a b
=========================================
2 1 2
3 2 4

쿼리의 처리 순서로 보면 inst_num() -> groupby_num() -> orderby_num() 의 순서로 적용됨. 질의문 사용시에 이를 고려하셔서 필요한 함수를 사용하시면 될 것임.

즉, MySQL의 LIMIT를 큐브리드 형식으로 바꾸려면 다음과 같이
질의문에 group by, order by 절이 없으면: LIMIT -> WHERE 절에 inst_num() 조건 추가
질의문에 group by 절이 있고 order by 절이 없으면: LIMIT -> HAVING 절에 groupby_num() 조건 추가
질의문에 order by 절이 있으면: LIMIT -> ORDER BY .. FOR 절에 orderby_num() 조건 추가하면 됨