使用num_rows列创建视图-MySQL [英] Create a view with column num_rows - MySQL
问题描述
我需要创建一个视图,该视图具有一个名为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.
推荐答案
我为此找到了解决方案:
I found a solution for this:
首先创建一个函数:
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.
SET @ var = 0;
SET @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.
这篇关于使用num_rows列创建视图-MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!