问题描述
我需要创建一个视图,在结果中自动添加虚拟行号.这里的图表是完全随机的,我想要实现的只是动态创建的最后一列.
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.
这篇关于MySQL 视图中的行排名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!