2013/03/08 - [프로그램 자료/MS-SQL] - MSSQL 순번 출력, 그룹순번출력 row , row_number
[개발환경]
MariaDb 10.0
로우넘버를 생성할 때 정렬이 제대로 되지 않는다면 다음과 같이 limit를 사용해서 테이블을 새로 만들어야 한다.
웃기는 버그가 있다.
SELECT @RN := (CASE WHEN @TID = T.PDEPT THEN @RN + 1 ELSE 1 END) AS RN, @TID := T.PDEPT, T.*
FROM(
SELECT RD.ID, RD.FULLPATH AS PDEPT, DP.CDEPT, DP.POLICYID
FROM DEPT AS RD
LEFT JOIN (
SELECT MAP.*, CD.FULLPATH AS CDEPT
FROM POLICY_DEPT AS MAP
JOIN DEPT AS CD ON MAP.DEPTID = CD.ID
) AS DP ON RD.FULLPATH LIKE CONCAT(DP.CDEPT, '%')
LIMIT 18446744073709551615
) AS T
CROSS JOIN (SELECT @RN := 0, @TID:='') AS R
ORDER BY PDEPT DESC, CDEPT DESC
예전 다른 DB에서 작업을 할 때 페이징이나 또는 그 외의 이유로 순번을 작성할 때는 row_number, rank 같은 함수를 사용했던 거 같다.
현재는 MariaDB 10.0을 사용하고 있는데 이 아이는 그런게 없더라.
rank, row_number, cte 등은 MariaDB 10.2 부터 지원을 한다고하니 그 이후 버전을 사용하자 ㅠㅠ
우선 간단한 row_number를 구현해보자.
다음과 같이 변수만 선언된 테이블을 크로스 조인해서 해당 변수를 올리면 된다.
SELECT (@RN := @RN + 1) AS ROWNUM, T.*
FROM TABLE AS T
CROSS JOIN (SELECT @RN := 0) AS R;
문제는 다음과 같이 group by 처럼 정렬한 아이들의 순서이다.
보통은 row_number() over (parition by .... oder by ...) 하고 사용했던 아이다.
갑자기 머리가 아파진다. 저걸 어찌할까.
SELECT ROW_NUMBER() OVER(PARTITION BY COL1, COL2 ORDER BY COL3) AS ROWNUM
, T.*
FROM TABLE AS T;
의외로 모두 한 곳에 몰아두고 다음과 같이 모두 같을 때만 1로 초기화 하면된다.
@RN의 위치는 @COL1, @COL2의 변수할당보다 먼저 나와야 정상적으로 출력이 되며 이때 order by 구문을 적절하게 사용하여 미리 정렬을 해주어야 정상적으로 동작한다.
SELECT @RN := (CASE WHEN @COL1 = T.COL1 AND @COL2 = T.COL2 THEN @RN + 1 ELSE 1 END) AS ROWNUM
, @COL1 := T.COL1
, @COL2 := T.COL2
, T.*
FROM TABLE AS T
CROSS JOIN (SELECT @RN := 0, @COL1 = '', @COL2 = '') AS R
ORDER BY COL1, COL2, COL3
