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 |