[MySQL] ROW_NUMBERS() 함수 쿼리.

Oracle에는 ROW_NUMBER()라는 함수가 있다. SELECT 문에서 주로 사용하는데 레코드에 번호를 먹여주는 것으로 다음과 같이 나온다.

[code sql]
+----+------------+
|   rk   |     name           |
+----+------------+
|   1    |     Hyun           |
|   2    |     Tacitus         |
+----+------------+
[/code]

테이블 스키마에 rk 라는 컬럼이 없는데도 위의 경우처럼 결과를 보여준다.

문제는 MySQL은 이것을 지원하지 않는다. 대부분 idx라는 컬럼을 만들고 auto_increament 제약사항을 줘서 Primary key 로 정해서 사용한다. 그래서 row_number()라는 함수가 없어도 필요로 하는 기능을 수행할 수 있다. 그런데 이러한 키가 없을 경우는 어떻게 할 것인가?

검색을 하다보니 이러한 고민이 상당이 많이 나왔고 적당한 해결책을 찾아냈다.
http://jimmod.com/blog/2008/09/displaying-row-number-rownum-in-mysql/

[code sql]
mysql> SELECT @row:=@row+1 as rk, it.TABLE_SCHEMA, it.TABLE_NAME FROM (SELECT @row:=0) AS r, INFORMATION_SCHEMA.TABLES AS it WHERE it.TABLE_NAME='xe_session';
[/code]

mysql 5.x의 INFOMATION_SCHEMA에 번호를 먹여봤다. 'xe_session'이라는 테이블을 가진 데이터베이스를 검색하는 쿼리인데 스키마에는 rk가 없다. 그래서 위의 쿼리처럼 임의로 rk를 지정해줄 수 있다.

그런데 rk는 말했다시피, 레코드의 컬럼이 아니다. 그래서 WHERE 조건문에서 사용할 수 없다. rk를 이용해서 특정 번호 이상 혹은 이전값을 가지고 와야할 경우가 생기는데 그럴때는 새롭게 가상의 테이블을 만든다.

[code sql]
mysql> SELECT * FROM  (SELECT @row:=@row+1 as rk, it.TABLE_SCHEMA, it.TABLE_NAME FROM (SELECT @row:=0) AS r, INFORMATION_SCHEMA.TABLES AS it WHERE it.TABLE_NAME='xe_session') rank WHERE rk > 10
mysql> SELECT * FROM  (SELECT @row:=@row+1 as rk, it.TABLE_SCHEMA, it.TABLE_NAME FROM (SELECT @row:=0) AS r, INFORMATION_SCHEMA.TABLES AS it WHERE it.TABLE_NAME='xe_session') rank WHERE rk BETWEEN 10 AND 24
[/code]

Mysql도 가능하다.