🛢 3. Database/3-1 Mysql
[Database] 12. 페이징 처리
달님🌙
2021. 9. 27. 13:32
반응형
[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;
반응형