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 
@rownum:=@rownum+1 ISN, A.*
FROM
youtuMember A,
(SELECT @rownum:=0) R;












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-/)

 

감사합니다.




출처 - http://k.daum.net/qna/view.html?qid=3xJdG








쿼리를 날리다 보면, 필요에 따라 그룹별로 순위를 매겨야 할 때 있다.
이에 대해 오라클에서는 그러한 기능을 제공하는데,
아래가 바로 그 예이다.

[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
---------- -------------------- ------------------ ---------- ----------
      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 개의 행이 선택되었습니다.



상기 쿼리는,
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










Posted by linuxism

댓글을 달아 주세요

  1. Hoguz 2018.06.25 15:40 신고  댓글주소  수정/삭제  댓글쓰기

    너무나 좋은 글 잘 보고 갑니다^^