[Database] 12. 페이징 처리

 

[MySQL]에서 Oracle처럼 ROWNUM을 사용하여 번호매기기

 

 

1) SET 구문을 사용하여 ROWNUM 값을 초기화 후 조회

SET @rownum:=0;
SELECT
@rownum:=@rownum+1 AS `NUM`, tbl.*
FROM lecture.idol_member tbl;

 

2) WHERE 절에서 초기화

SELECT
  @rownum:=@rownum+1 AS `NUM`, 
  tbl.*
FROM lecture.idol_member tbl
	--WHERE (@rownum:=0)=0; 초기화 하지 않은 경우

SELECT
  @rownum:=@rownum+1 AS `NUM`, 
  tbl.*
FROM lecture.idol_member tbl
WHERE (@rownum:=0)=0;

 

3) 페이징(Paging) 처리(행 제한하기)

    SET @rownum:=0;
    SELECT
        @rownum:=@rownum+1 AS `NUM`, 
        tbl.*
    FROM lecture.idol_member tbl
    limit 0, 5;

    SET @rownum:=5;
    SELECT
        @rownum:=@rownum+1 AS `NUM`, 
        tbl.*
    FROM lecture.idol_member tbl
    limit 5, 5;

    SET @rownum:=10;
    SELECT
        @rownum:=@rownum+1 AS `NUM`, 
        tbl.*
    FROM lecture.idol_member tbl
    limit 10, 5;

    SET @rownum:=15;
    SELECT
        @rownum:=@rownum+1 AS `NUM`, 
        tbl.*
    FROM lecture.idol_member tbl
    limit 15, 5;

    SET @rownum:=20;
    SELECT
        @rownum:=@rownum+1 AS `NUM`, 
        tbl.*
    FROM lecture.idol_member tbl
    limit 20, 5;

    SET @rownum:=25;
    SELECT
        @rownum:=@rownum+1 AS `NUM`, 
        tbl.*
    FROM lecture.idol_member tbl
    limit 25, 5;

 

4) 실습

Set @NUM :=0;
SELECT @NUM:=@NUM+1 AS `NO`, T.* FROM `MEMBER` T LIMIT 0,5;

Set @NUM :=5;
SELECT @NUM:=@NUM+1 AS `NO`, T.* FROM `MEMBER` T LIMIT 5,5;

Set @NUM :=10;
SELECT @NUM:=@NUM+1 AS `NO`, T.* FROM `MEMBER` T LIMIT 10,5;

Set @NUM :=15;
SELECT @NUM:=@NUM+1 AS `NO`, T.* FROM `MEMBER` T LIMIT 15,5;

Set @NUM :=20;
SELECT @NUM:=@NUM+1 AS `NO`, T.* FROM `MEMBER` T LIMIT 20,5;

Set @NUM :=25;
SELECT @NUM:=@NUM+1 AS `NO`, T.* FROM `MEMBER` T LIMIT 5,5;

Set @NUM :=30;
SELECT @NUM:=@NUM+1 AS `NO`, T.* FROM `MEMBER` T LIMIT 30,5;