MySQL 视图中的行排名

2021-12-29 00:00:00 sql ranking mysql view

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

I need to create a view that automatically adds virtual row number in the result. the graph here is totally random all that I want to achieve is the last column to be created dynamically. 

> +--------+------------+-----+
> | 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

推荐答案

使用:

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

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

It's not an ideal manner of doing this, because the query for the num value will execute for every row returned. A better idea would be to create a NUMBERS table, with a single column containing a number starting at one that increments to an outrageously large number, and then join & reference the NUMBERS table in a manner similar to the variable example that follows.

您可以定义一个变量以获得伪行号功能，因为 MySQL 没有任何排名功能:

You can define a variable in order to get psuedo row number functionality, because MySQL doesn't have any ranking functions:

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

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

    • The SELECT @rownum := 0 defines the variable, and sets it to zero.
    • The r is a subquery/table alias, because you'll get an error in MySQL if you don't define an alias for a subquery, even if you don't use it.

      • 如果你这样做，你会得到 1351 错误，因为 由于设计原因，您不能在视图中使用变量.此处记录了错误/功能行为.

      If you do, you'll get the 1351 error, because you can't use a variable in a view due to design. The bug/feature behavior is documented here.

相关文章