[MySQL] Oracle(오라클)의 ROWNUM, ROW_NUMBER (PARTITION BY) 구현하기

1. ROWNUM 구현하기

SELECT @RNUM := @RNUM + 1 AS RNUM             , A.*     FROM 테이블 A            , (SELECT @RNUM := 0) B

2. ROW_NUMBER(PARTITION BY ...) 구현하기

참고 : http://www.explodybits.com/2011/11/mysql-row-number/

ROW_NUMBER(PARTITION BY 컬럼1 ORDER BY 컬럼2) SELECT @ROW_NUM := IF(@PREV_VALUE = A.컬럼1, @ROW_NUM + 1, 1) AS ROW_NUMBER           , @PREV_VALUE := A.컬럼1           , A.*   FROM 테이블 A          , (SELECT @ROW_NUM := 1) X          , (SELECT @PREV_VALUE := '') Y  ORDER BY A.컬럼2 -- 참고 샘플 SELECT  @row_num := IF(@prev_value=o.Customer,@row_num+1,1) AS RowNumber        ,o.Customer        ,o.OrderDate        ,o.Amount        ,@prev_value := o.Customer   FROM Orders o,       (SELECT @row_num := 1) x,       (SELECT @prev_value := '') y   ORDER BY o.Customer, o.OrderDate DESC