MySQL에서 Oracle의 ROWNUM같은 기능을 사용할려면 어떻게 해야 할까?
다음과 같은 방법으로 손쉽게 사용 가능하다.
SELECT
@ROWNUM := @ROWNUM + 1 AS ROWNUM,
TEST_TABLE.*
FROM
TEST_TABLE,
(SELECT @ROWNUM := 0) R
ROWNUM을 이용하여 다른 작업을 하기위해서는 다음과 같이 서브쿼리를 이용하면 된다.
SELECT
A.*
FROM
(
SELECT
@ROWNUM := @ROWNUM + 1 AS ROWNUM,
TEST_TABLE.*
FROM
TEST_TABLE,
(SELECT @ROWNUM := 0) R
) A
WHERE
A.ROWNUM < 100
출처 - http://it79.egloos.com/557555
SELECT @RNUM := @RNUM + 1 AS ROWNUM, t.*
FROM
(
SELECT *
FROM table
ORDER BY column1
) t,
( SELECT @RNUM := 0 ) R
출처 - http://dhplanner.blogspot.kr/2009/07/mysql-rownum-%EA%B5%AC%ED%98%84%ED%95%98%EA%B8%B0.html
-- Select시 보여주기
select *,@ROWNUM := @ROWNUM +1 as ROWNUM
from [TABLE_NAME], (select @ROWNUM := 0) R
-- Row에 저장하기
set @n:=0;
update [TABLE_NAME] set [COLUMN] = @n := @n+1
[출처
] MySQL ROWNUM 추가 방법|작성자 감자
안녕하세요 . MySQL 에서는 일반적인 프로그램에서 사용할 수 있는 변수처럼, 사용자 정의 변수를 이용하여 rownum을 구현할 수 있는데요.
-- (1) select @rank:=@rank+1 rank, a.* from 대상테이블 a, (select @rank :=0 from dual) b;
또는,
-- (2) set @rank :=0; select @rank:=@rank+1 rank, a.* from 대상테이블 a
와 같이 사용할 수 있습니다.
둘다 동일한 의미의 쿼리이지만, (1) 의 쿼리는 1개의 쿼리로 변수의 초기화를 수행할 수 있는 반면, (2)의 쿼리는 2개의 쿼리를 날려야 하는 수고(?)가 있지요.
반면에, (1)의 쿼리는 직접 쿼리를 날려서 시뮬레이션 하기 이전에는 실제적으로 결과가 어떻게 나오게 될 지 모르는 경우가 많아서요. 저 개인적으로는 (2)의 쿼리를 선호하는 편입니다. ^^;; (제가 아직 미숙해서..)
mysql에서의 사용자 정의변수에 대한 내용을 더 보고 싶으시다면, http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21582&cat1=9&cat2=292&cat3=0&lang=k 에서 조금 더 알아보실 수 있으실 겁니다. (한글 메뉴얼입니다 -0-/)
감사합니다. |
쿼리를 날리다 보면, 필요에 따라 그룹별로 순위를 매겨야 할 때가 있다.
이에 대해 오라클에서는 그러한 기능을 제공하는데,
아래가 바로 그 예이다.
[Oracle]
SELECT empno, ename, job, sal,
ROW_NUMBER() OVER(PARTITION BY job ORDER BY sal) AS rnum
FROM scott.emp;
<<결과>>
EMPNO ENAME JOB SAL RNUM 14 개의 행이 선택되었습니다. |
상기 쿼리는,
emp 테이블의 JOB을 기준으로 하여 그룹을 정하고 (PARTITION BY job), -- 1
sal을 기준으로 하여 순위를 매겨(ORDER BY sal),
각각의 행에 ROW_NUMBER를 부여하겠다는 의미이다. -- 2
여기서 'PARTITION BY job'은 job별 정렬을 발생시킨다.
즉, 최종 결과물의 넘버링은 ORDER BY job, sal의 순으로 결과가 나오는 것이다.
[MySQL]
그런데, 불행하게도..... MySQL에는 저 기능이 없다.
그렇기 때문에 우리의 친구 꼼수(?)를 이용하여 저것을 구현해 내야 하는데.....
SELECT empno, ename, job, sal, rnum
FROM (
SELECT a.*,
(CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
(@vjob:=a.job) vjob
FROM emp a, (SELECT @vjob:='', @rownum:=0 FROM DUAL) b
ORDER BY a.job, a.sal
) c;
<<결과>>
| +-------+--------+-----------+------+------+ | empno | ename | job | sal | rnum | +-------+--------+-----------+------+------+ | 7902 | FORD | ANALYST | 3000 | 1 | | 7788 | SCOTT | ANALYST | 3000 | 2 | | 7369 | SMITH | CLERK | 800 | 1 | | 7900 | JAMES | CLERK | 950 | 2 | | 7876 | ADAMS | CLERK | 1100 | 3 | | 7934 | MILLER | CLERK | 1300 | 4 | | 7782 | CLARK | MANAGER | 2450 | 1 | | 7698 | BLAKE | MANAGER | 2850 | 2 | | 7566 | JONES | MANAGER | 2975 | 3 | | 7839 | KING | PRESIDENT | 5000 | 1 | | 7654 | MARTIN | SALESMAN | 1250 | 1 | | 7521 | WARD | SALESMAN | 1250 | 2 | | 7844 | TURNER | SALESMAN | 1500 | 3 | | 7499 | ALLEN | SALESMAN | 1600 | 4 | +-------+--------+-----------+------+------+ 14 rows in set (0.00 sec) |
어때... 결과가 같아 보이는가?
자, 그럼 쿼리를 뜯어보자.
여기서 궁금하게 생각되는 부분은 아래 3개의 쿼리라고 예상 된다.
1. (CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
--> 이전 job 필드와 동일한 그룹인가를 판별하고, 그룹에 따라 순번을 부여하기 위함이며,
테이블에서 각각의 행을 읽을 때마다,
변수 @vjob 값이 지금 새로 읽은 job 과 같다면 변수 @rownum을 1증가 시키고,
그렇지 않은 경우(@vjob이 현재 읽은 job값과 같지 않다면) @rownum을 1로 초기화 시킨다.
2. (@vjob:=a.job) as vjob
--> 테이블에서 각각의 행을 읽을 때마다,
그룹 판별을 위해 현재 읽고 있는 행의 job값을 변수 @vjob에 입력
3. (SELECT @vjob:='', @rownum:=0 FROM DUAL) b
--> 원래는 쿼리를 수행하기 이전에,
SET @vjob:=0, @rownum:=0; 을 수행하여 변수를 초기화 해야 한다.
만약 해주지 않으면, NULL 값이 들어가게 된다.
하지만 그럴 경우 쿼리가 2번 수행되어야 하기 때문에,
하나의 쿼리로 만들기 위해서 이런 식의 서브 쿼리를 이용한 것이다.
이 서브쿼리는 초기 테이블 확인시 1회만 수행되고,
이후부터는 열람되지 않는다.
!! 주의 !!
서브쿼리 안에서의 결과값만 가지고 현재의 결과값을 얻고자 할 때,
변수가 되는 항목의 값을 동일한 자료형으로 맞춰주지 않으면,
정상적인 결과값이 나오지 않는다.
가령 위의 예를 이용하자면, @vjob의 초기값을 @vjob:=0 으로 수행 하고
서브쿼리만을 수행하면 정상적인 결과값이 나오지 않게 된다.
한 번 해보자~
이 3가지를 이해한다면 아마 이해할 수 있을 것이라 생각되지만,
한 가지 짚고 넘어가야 할 것이 있다.
Q. 우리가 흔히 쓰는 SELECT 문장의 수행순서는 어떻게 될까?
무슨의미냐 하면..
위에서 사용한 것처럼 변수를 이용한 SELECT 내 연속적인 값의 할당은,
수행결과에 영향을 미치게 되지 않을까?
라는 질문이다.
흠.. 내가 말을 써놓고 난해하군..
예제를 보도록 하자.
<<예제>>
SET @val1=0, @val2=0; #아까도 말했듯이 변수 초기화는 먼저 선행되어야 한다.
SELECT @val1:=@val1+1, @val2:=@val1+1, @val2:=0, @val1=@val2+1
FROM DUAL;
자.... 당신이 예상하는 결과는?.....
<<쿼리 수행 결과>>
| +----------------+----------------+----------+---------------+ | @val1:=@val1+1 | @val2:=@val1+1 | @val2:=0 | @val1=@val2+1 | +----------------+----------------+----------+---------------+ | 1 | 2 | 0 | 1 | +----------------+----------------+----------+---------------+ 1 row in set (0.00 sec) |
상기와 같이 SELECT 내 수행 결과는,
왼쪽에서 오른쪽으로 순차적인 수행이 이루어짐을 알 수 있다.
즉, @val1:=@val1+1 → @val2:=@val1+1 → @val2:=0 → @val1=@val2+1
로 수행 순서가 정해진다는 의미.
그러므로,
변수를 이용한 SELECT를 이용할 때는 반드시 수행순서를 염두해 두고 쿼리를 작성하도록 하자.
PS : 오라클에는 예제 테이블이 있지만 MySQL 에는 없으니
혹시 테스트 해보고 싶은 사람은 아래 쿼리를 수행해서 테스트 해보도록...
CREATE TABLE emp (
empno INT,
ename VARCHAR(30),
job VARCHAR(30),
sal INT
)ENGINE=INNODB DEFAULT CHAR SET=UTF8;
INSERT INTO emp
VALUES
| (7902,'FORD','ANALYST',3000), |
| (7788,'SCOTT','ANALYST',3000), |
| (7369,'SMITH','CLERK',800), |
| (7900,'JAMES','CLERK',950), |
| (7876,'ADAMS','CLERK',1100), |
| (7934,'MILLER','CLERK',1300), |
| (7782,'CLARK','MANAGER',2450), |
| (7698,'BLAKE','MANAGER',2850), |
| (7566,'JONES','MANAGER',2975), |
| (7839,'KING','PRESIDENT',5000), |
| (7654,'MARTIN','SALESMAN',1250), |
| (7521,'WARD','SALESMAN',1250), |
| (7844,'TURNER','SALESMAN',1500), |
| (7499,'ALLEN','SALESMAN',1600); |
출처 - http://blackbull.tistory.com/43
'DB > MySQL' 카테고리의 다른 글
| mysql - BLOB, TEXT 데이터 형식 (0) | 2012.08.09 |
|---|---|
| mysql - 파일에서 테이블로 insert (0) | 2012.07.13 |
| Mysql DB 복호화 (password(), md5(), crypt()) (4) | 2012.05.25 |
| mysql에서 limit 사용 (0) | 2012.05.02 |
| MySQL 로그 파일 관리 2 - 로그 파일 남기기 (0) | 2012.05.02 |
