创建一个带有 num_rows 列的视图 - MySQL

2021-12-29 00:00:00 mysql views

我需要创建一个视图，它有一个名为 row_num 的列，将在其中插入行号，就像普通表中的自动增量一样.

I need to create a view that has a column named row_num where it will be inserted the row number, just like an auto increment in a normal table.

假设我有一张普通的桌子:

Let's say I've this normal table:

| country | name | age | price |
--------------------------------
| US      | john | 22  | 20    |
| France  | Anne | 10  | 15    |
| Sweden  | Alex | 49  | 10    |

等等……

我要创建的视图是:

    | country | name | price | row_num |
    ------------------------------------
    | US      | john |  20   |    1    |
    | France  | Anne |  10   |    2    |
    | Sweden  | Alex |  5    |    3    |

等等……

我可以通过一次选择生成 row_num:

I can generate the row_num with a single select:

SELECT @i:=@i+1 AS row_num, testing.country, testing.name, testing.price
FROM testing testing,(SELECT @i:=0) derivedTable
order by name

但我的问题是将上面的查询与创建视图的查询结合起来.这是我正在尝试的组合查询:

But my problem is to combine the query above with the query creating the view. This is the combined query I'm trying:

CREATE OR REPLACE view vwx (country, name, price, num_row) AS SELECT mytable.country, mytable.name, mytable.price, @i:=@i+1 AS row_number
    FROM testing testing,(SELECT @i:=0) derivedTable
    order by name;

我收到以下错误:#1351 - 视图的 SELECT 包含变量或参数

I get the following error: #1351 - View's SELECT contains a variable or parameter

我知道我不能在带有视图的选择中使用选择，但我没有看到以我想要的方式执行此视图的其他方法，但我确定有一种方法可以做到这一点，但我只是不这样做不知道怎么办可能有函数或过程，但我对它们真的很陌生，所以我不习惯在 mysql 中创建函数或过程.

I know I can't use a select inside a select with views but I don't see other way to do this view the way I want, but I'm sure there is a way to do this but I just don't know how. Probably with functions or procedures but I'm really new to them so I'm not comfortable with creating functions or procedures in mysql.

我希望我说清楚了，否则我很乐意更详细地解释自己.

I hope I made myself clear otherwise I'm more than happy to explain myself in further detail.

推荐答案

我找到了解决方案:

首先创建一个函数:

delimiter //

CREATE FUNCTION `func_inc_var_session`() RETURNS int
    NO SQL
    NOT DETERMINISTIC
     begin
      SET @var := @var + 1;
      return @var;
     end
     //

delimiter ;

然后将@var 设置为您想要开始的数字.在这种情况下为零.

Then set @var to the number you want to start with. In this case zero.

设置@var=0;

然后创建如下视图:

CREATE OR REPLACE VIEW myview (place, name, hour, price, counter) 
AS SELECT place, name, hour, price, func_inc_var_session() 
FROM yourtable
WHERE input_conditions_here;

这里的技巧是您可能会在计数器列上看到 NULL.如果发生这种情况，请再次将 @var 设置为您的号码，然后再次执行 SELECT *，您将看到计数器列正确填充.

The trick here is that you may see NULL on the counter column. If this happens please set @var again to your number and then do the SELECT * again and you'll see the counter column properly populated.

相关文章