MySQLにはOracleなんかと違ってROW_NUMBER関数がないので、ユーザ変数を使って振ってやる必要がある。
便利なのでメモ。
普通にSELECTした結果。
mysql> SELECT TABLE_NAME FROM information_schema.TABLES LIMIT 10;
+---------------------------------------+
| TABLE_NAME |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
+---------------------------------------+
10 rows in set (0.00 sec)
mysql>
行番号つけた結果。
mysql> SET @rownum=0; SELECT @rownum:=@rownum+1 as ROW_NUM, TABLE_NAME FROM information_schema.TABLES LIMIT 10;
Query OK, 0 rows affected (0.00 sec)
+---------+---------------------------------------+
| ROW_NUM | TABLE_NAME |
+---------+---------------------------------------+
| 1 | CHARACTER_SETS |
| 2 | COLLATIONS |
| 3 | COLLATION_CHARACTER_SET_APPLICABILITY |
| 4 | COLUMNS |
| 5 | COLUMN_PRIVILEGES |
| 6 | ENGINES |
| 7 | EVENTS |
| 8 | FILES |
| 9 | GLOBAL_STATUS |
| 10 | GLOBAL_VARIABLES |
+---------+---------------------------------------+
10 rows in set (0.00 sec)
mysql>