MySQL视图中的行排序

Question

我需要创建一个视图，在结果中自动添加虚拟行号。这里的图是完全随机的，我想要实现的是最后一个要动态创建的列。

> +--------+------------+-----+
> | id     | variety    | num |
> +--------+------------+-----+
> | 234    | fuji       |   1 |
> | 4356   | gala       |   2 |
> | 343245 | limbertwig |   3 |
> | 224    | bing       |   4 |
> | 4545   | chelan     |   5 |
> | 3455   | navel      |   6 |
> | 4534345| valencia   |   7 |
> | 3451   | bartlett   |   8 |
> | 3452   | bradford   |   9 |
> +--------+------------+-----+

查询：

SELECT id, 
       variety, 
       SOMEFUNCTIONTHATWOULDGENERATETHIS() AS num 
  FROM mytable

Answer 1

使用：

SELECT t.id,
       t.variety,
       (SELECT COUNT(*) FROM TABLE WHERE id < t.id) +1 AS NUM
  FROM TABLE t

这不是一种理想的方法，因为对num值的查询将对返回的每一行执行。一个更好的方法是创建一个NUMBERS表，其中一个列包含一个数字，从一个数字开始增长到一个非常大的数字，然后以类似于下面的变量示例的方式加入和引用NUMBERS表。

MySQL排名，或缺乏排名

您可以定义一个变量以获得psuedo行号功能，因为MySQL没有任何排序函数：

SELECT t.id,
       t.variety,
       @rownum := @rownum + 1 AS num
  FROM TABLE t,
       (SELECT @rownum := 0) r

• SELECT @rownum := 0定义变量，并将其设置为零。
• r是子查询/表别名，因为如果您不为子查询定义别名，即使不使用它，也会在MySQL中出现错误。

不能在MySQL视图中使用变量

如果这样做，您将得到1351错误，因为由于设计原因，无法在视图中使用变量。错误/特性行为在这里有文档记录。。

Answer 2

Oracle有一个rowid伪列。在MySQL中，您可能不得不变得丑陋：

SELECT id,
       variety,
       1 + (SELECT COUNT(*) FROM tbl WHERE t.id < id) as num
  FROM tbl

这个查询是在我头上没有测试过的，所以要用一点盐的方法来理解它。此外，它假定您希望按照某种排序标准(在本例中为id)对行进行编号，而不是按照问题中所示的任意编号。