bitscn.com
mysql 如何实现 oracle 的 rownum2010/01/28 23:56mysql> select * from frutas;
+-----------+----------+
| nombre    | color    |
+-----------+----------+
| fresa     | rojo     |
| platano   | amarillo |
| manzana   | verde    |
| uva       | verde    |
| pera      | verde    |
| mandarina | naranja  |
| melocoton | marron   |
| limon     | amarillo |
+-----------+----------+
8 rows in set (0,00 sec)
【一条sql语句完成，不set变量】
select @rownum:=@rownum+1 as rownum, frutas.*
from (select @rownum:=0) r, frutas;
mysql> select @rownum:=@rownum+1 as rownum, frutas.*
-> from (select @rownum:=0) r, frutas;
+--------+-----------+----------+
| rownum | nombre    | color    |
+--------+-----------+----------+
|      1 | fresa     | rojo     |
|      2 | platano   | amarillo |
|      3 | manzana   | verde    |
|      4 | uva       | verde    |
|      5 | pera      | verde    |
|      6 | mandarina | naranja  |
|      7 | melocoton | marron   |
|      8 | limon     | amarillo |
+--------+-----------+----------+
8 rows in set (0,00 sec)
但是如何把这个查询放在 create view 里面
mysql> create view vw_frutas as select @rownum:=@rownum+1 as rownum, frutas.*
->  from (select @rownum:=0) r, frutas;
error 1351 (hy000): view's select contains a variable or parameter
本文出自“老鸟的大心脏!”
bitscn.com