我需要创建一个视图,其中有一个名为row_num的列,它将插入行号,就像普通表中的自动增量一样.
假设我有这个普通表:
| 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 |
等等…
我可以用一个select生成row_num:
SELECT @i:=@i+1 AS row_num, testing.country, testing.name, testing.price
FROM testing testing,(SELECT @i:=0) derivedTable
order by name
但我的问题是将上面的查询与创建视图的查询结合起来.
这是我正在尝试的组合查询:
CREATE OR REPLACE view vwx (country, name, price, num_row) AS SELECT mytable.country, mytable.name, mytable.price, @i:=@i+1 AS row_numb