개발자의 끄적끄적
개발/sql
[sql/mysql] mysql row_number() 사용해서 행번호 붙이는 방법
효벨 2021. 11. 12. 02:00
[sql/mysql] mysql row_number() 사용해서 행번호 붙이는 방법
mysql 에서
특정 테이블의
행번호를 붙여서 데이터를 조회하고 싶은 경우가 있습니다.
그럴때는 아래 나와있는
row_number 를 사용하시면 됩니다.
아래는 기본 사용법 입니다.
row_number() over(order by id desc)row_number() over(정렬기준)
이런식으로 사용하시면 됩니다.
예를들어
member 라는 테이블에
행번호를 붙이고 싶은데 id 역순으로 정렬을 하고 싶으면
아래와 같이 쿼리를 작성하시면 됩니다.
결과는 이렇게 num 라는 행번호가 포함된
테이블이 나오게 됩니다!
| num | id | name |
| 1 | test2 | 테스트2 |
| 2 | test1 | 테스트1 |
참고들 하세요!
MySQL ROW_NUMBER ORDER BY 정렬한 후 번호 매기기
아..오라클은 ROW_NUMBER() OVER (PARTITION BY ...ORDER BY) 로 정렬한 후 번호 매기기가 매우 쉽다.
하지만 우리의 MySql 은 그러한 함수가 없다.
그렇지만 MySql은 쿼리문에서 변수를 사용할 수 있는 막강한 기능이 있다.
변수를 이용하여 order by 한 쿼리에 rownumber 를 구하는 방법에 대해 알아보자.
우선 감을 잡기 위해 제일 기본이 되는 쿼리문 부터 보겠다.
기본 SELECT 문이다.
SELECT T1.GG4ID AS GG4ID
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
FROM MART_GUGAN4_STAT_15MIN T1
WHERE BASIC_DAY = '20151201'
ORDER BY GG4ID, HOUR, MIN
결과는 아래와 같다.
정말 말그대로 조건에 맞게 SELECT 해온 결과 이다.
이제 이 쿼리문에 ROWNUMBER를 매겨보자.
딱 2가지만 추가해 주면 된다.
SELECT T1.GG4ID AS GG4ID
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
, @rownum := @rownum+1 AS RNUM
FROM MART_GUGAN4_STAT_15MIN T1, (SELECT @rownum :=0) AS R
WHERE BASIC_DAY =
'20151201'
ORDER BY GG4ID, HOUR, MIN
@rownum이라는 변수를 사용했다.
(SELECT @rownum :=0) AS R 는 @rownum을 0으로 초기화해 SELECT 하겠다 이다.
그리고 @rownum := @rownum+1 AS RNUM 은 행을 불러올때마다 1을 더해서 출력하겠다 이다.
말그대로 변수를 선언하고 변수에 1씩을 더해 행에 가져오는 것이다.
결과는 위와 같이 출력된다.
그럼 여기서 한발짝 더 나아가 보자. 요즘 JOIN 없이 SELECT 하는게 있을까 싶어..
JOIN을 사용해 보자.
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
FROM MART_GUGAN4_STAT_15MIN T1
LEFT JOIN MART_GUGAN4 T2 ON (T2 .GG4ID = T1. GG4ID)
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%'
ORDER BY GUGANNAME, DIRECTION, HOUR, MIN
첫번째 쿼리문에 JOIN을 사용해 값을 더 가져오고 조건도 더 추가 했다.
여기에 ROWNUMBER을 매겨보자. 두가지만 추가 하면 되니깐...간단하겠지...
생각하여
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
, @rownum := @rownum+1 AS RNUM
FROM MART_GUGAN4_STAT_15MIN T1, (SELECT
@rownum := 0) AS R
LEFT JOIN MART_GUGAN4 T2 ON (T2 .GG4ID = T1. GG4ID)
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%'
ORDER BY GUGANNAME, DIRECTION, HOUR, MIN
위와 같이 추가를 해줬다. 하지만 결과는.
아.. 위치가 잘못됬구나 싶어
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
, @rownum := @rownum+1 AS RNUM
FROM MART_GUGAN4_STAT_15MIN T1
LEFT JOIN MART_GUGAN4 T2 ON (T2
.GG4ID = T1. GG4ID)
, (SELECT @rownum := 0) AS R
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%'
ORDER BY GUGANNAME, DIRECTION, HOUR, MIN
위치를 바쿼 출력했다. 오류가 안난다. 하지만 결과는.
꼬였다.. 조인을 하는 시점에서 @rownum이 잘못 더해지고 있었다.
이런 문제를 해결하는 방법은 서브쿼리를 사용하여 @rownum이 더해지는 시점을 바꾸는 것이다.
SELECT A.*
, @rownum := @rownum+1 AS RNUM
FROM (
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
FROM MART_GUGAN4_STAT_15MIN T1
LEFT JOIN MART_GUGAN4 T2 ON (T2 .GG4ID = T1.
GG4ID)
JOIN (SELECT @rownum := 0) AS R
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%'
ORDER BY GUGANNAME, DIRECTION, HOUR, MIN) AS A
참고) JOIN (SELECT @rownum := 0) AS R 과 ,(SELECT @rownum := 0) AS R 은 같다. 표현방식이 다를뿐이다.
간단하다. 데이터를 만든 시점에서 @rownum을 더하느냐 만들면서 더하느냐의 차이다
결과를 보면 원하는데로. order by 되고 조건에 맞는 값을 가져온 후에 @rownum을 구한다.
여기서 RNUM을 컨트롤 하기 위해선 한번더 서브쿼리를 써주면 된다..
SELECT *
FROM ( SELECT A.*
, @rownum :=
@rownum + 1 AS RNUM
FROM (
SELECT T2 .GG4_NAME AS GUGANNAME
, T2 .GG4_DIRECTION AS DIRECTION
, T1 .BASIC_DAY AS DAY
, T1 .BASIC_HOUR AS HOUR
, T1 .BASIC_MIN AS MIN
, T1 .VOLUME AS VOLUME
FROM MART_GUGAN4_STAT_15MIN T1
LEFT JOIN MART_GUGAN4 T2 ON (T2 .GG4ID = T1. GG4ID)
JOIN ( SELECT @rownum := 0) R
WHERE BASIC_DAY = '20151201'
AND GG4_NAME LIKE '%') AS A
ORDER BY A.GUGANNAME,
A.DIRECTION, A.HOUR, A.MIN) AS B
WHERE RNUM BETWEEN 1 AND 10