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

- Moak

---

可能是MySQL中的ROW_NUMBER()的重复问题。 - Ciro Santilli OurBigBook.com

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排名功能欠缺

您可以定义一个变量来获取伪行号功能，因为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错误，因为由于设计原因，您不能在视图中使用变量。该错误/特性行为在此处有所记录。

- OMG Ponies

---

好的解决方案，但是我无法在我的视图中获取它： ＃1351-视图的SELECT包含变量或参数：/ - Moak

你可以在运行查询之前定义变量，因此不需要子查询。我不确定它是连接/游标特定的变量还是全局变量，所以你需要先弄清楚这一点。 - Tor Valamo

@Tor：如果是存储过程，但是OP特别要求在视图中完成。 - OMG Ponies

Answer 2

Oracle有一个rowid伪列。在MySQL中，您可能需要使用丑陋的方法：

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

这个查询只是我临时想到的，未经测试，所以请谨慎使用。此外，它假设您想根据某种排序标准（在本例中为id）对行进行编号，而不是问题中显示的任意编号。

- Marcelo Cantos

---

我认为你的意思是 (SELECT COUNT(*) FROM tbl WHERE t.id < id) + 1 AS num - COUNT 函数不接受 SELECT 语句作为参数: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count - OMG Ponies

+1：测试了修正后的版本，输出结果与预期结果相符。 - OMG Ponies