mysql 记录号_mysql记录集中记录序号,MySQL中实现rownum功能类似的语句_MySQL

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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！