Mysql records the serial number in a set, and MySQL implements the statement bitsCN.com with the rownum function.
How does MySQL implement Oracle ROWNUM2010/01/28 23: 56 mysql> SELECT * FROM frutas;
+ ----------- + ---------- +
| Nombre | color |
+ ----------- + ---------- +
| Fresa | rojo |
| Platano | amarillo |
| Manzana | verde |
| Ultraviolet A | verde |
| Pera | verde |
| Mandarina | naranja |
| Melocoton | marron |
| Limon | amarillo |
+ ----------- + ---------- +
8 rows in set (0, 00 sec)
[An SQL statement is completed without setting variables]
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 | ultraviolet A | verde |
| 5 | pera | verde |
| 6 | mandarina | naranja |
| 7 | melocoton | marron |
| 8 | limon | amarillo |
+ -------- + ----------- + ---------- +
8 rows in set (0, 00 sec)
But how to put this query in 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
This article is from the heart of laruence !"
BitsCN.com