创建一个列为num_rows的视图- MySQL

我需要创建一个视图，其中有一个名为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    |

等等…

我可以用一个单一的选择生成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_number
    FROM testing testing,(SELECT @i:=0) derivedTable
    order by name;

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

我知道我不能在select with views中使用select，但我没有看到其他方法可以按照我想要的方式来做这个视图，但我确信有一种方法可以做到这一点，但我只是不知道如何。可能是函数或过程，但我对它们真的很陌生，所以我不习惯在mysql中创建函数或过程。

我希望我已经说清楚了，否则我很乐意再详细解释一下。

我找到了一个解决方案:

首先创建函数:

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

然后将@var设置为您想要开始的数字。在本例中为0。

设置@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 *，您将看到计数器列正确填充

我尝试了func_inc_var_session函数的例子。

有一个会话变量初始化的小问题，我使用mysql IFNULL函数解决。

增强func_inc_var_session功能以下。

CREATE DEFINER=`root`@`localhost` FUNCTION `func_inc_var_session`() RETURNS int(11)
begin
  SET @var := IFNULL(@var,0) + 1;
  return @var;
end

当使用@dazito的解决方案时，您可能会遇到计数器从一个查询到另一个查询不断增加的问题，例如当您的应用程序重用会话时，例如JPA/Hibernate。例如:

查询1:

<>之前|国家|名称|价格| row_num------------------------------------美国|约翰| 20 | 1 ||法国|安妮| 10 | 2 |瑞典|亚历克斯| 5 | 3 |之前查询2:

<>之前|国家|名称|价格| row_num------------------------------------【美】约翰| 20 | 4 |法国|安妮| 10 | 5 |瑞典|亚历克斯| 5 | 6 |之前

等。

一个解决方案是将主查询与(一次性)调用计数器函数连接起来，并将函数参数化(下面的'reset'参数)，让它知道这是第一次调用。

<>之前分隔符//创建函数' func_inc_var_session '(复位位)返回int没有SQL不确定性开始如果复位，则SET @var:= 0;其他的@var:= IFNULL(@var,0) + 1;如果;返回@var;结束//分隔符;之前

现在，您可以在视图查询中调用该函数，并将reset参数设置为1，以将函数的计数器变量设置为0，并将0设置为增加计数器。当与函数连接时，函数只会以1作为参数调用一次，如下所示:

<>之前创建或替换视图country_view(国家，名称，价格，行数)SELECT国家，名称，价格，func_inc_var_session(0)从一个国家JOIN (SELECT func_inc_var_session(1)之前

现在您保证每次都是第1、2、3行

或者尝试如下->创建一个临时表并将您的数据插入其中

CREATE OR REPLACE TEMPORARY TABLE myview (
    country VARCHAR(250), 
    name VARCHA(50), 
    price VARCHAR(50), 
    row_num int(11)
);
SET @row_num = 0;
INSERT INTO myview (country,name,price,row_num)
    SELECT @row_num:=@row_num+1 
        as country,name,price,row_num 
    FROM testing;
SELECT * FROM myview;
+---------+------+------+-------+---------+
| country | name | age  | price | row_num |
+---------+------+------+-------+---------+
| Sweden  | Alex |   49 |    10 |       1 |
| France  | Anne |   10 |    15 |       2 |
| France  | Anne |   11 |    16 |       3 |
| US      | john |   22 |    20 |       4 |
+---------+------+------+-------+---------+

尝试将此查询应用于您的视图-

CREATE TABLE testing (
  id int(11) NOT NULL DEFAULT 0,
  country varchar(255) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  price int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO testing(id, country, name, age, price) VALUES
  (1, 'US', 'john', 22, 20),
  (2, 'France', 'Anne', 10, 15),
  (3, 'Sweden', 'Alex', 49, 10),
  (4, 'France', 'Anne', 11, 16);
SELECT
  t1.*, COUNT(*) row_num
FROM testing t1
  LEFT JOIN testing t2
    ON t2.name < t1.name OR (t2.name = t1.name AND t2.id <= t1.id)
 GROUP  BY t1.name, t1.id;
+----+---------+------+------+-------+---------+
| id | country | name | age  | price | row_num |
+----+---------+------+------+-------+---------+
|  3 | Sweden  | Alex |   49 |    10 |       1 |
|  2 | France  | Anne |   10 |    15 |       2 |
|  4 | France  | Anne |   11 |    16 |       3 |
|  1 | US      | john |   22 |    20 |       4 |
+----+---------+------+------+-------+---------+

这个查询不使用任何用户变量，所以它在视图中工作。
ON子句的附加条件有助于实现name字段的重复值。

MySql自8.0版本以来支持ROW_NUMBER()功能，允许您创建视图。旧方法的问题(这是对期望行为的模拟)是，在创建视图的时刻，(@row_number:=@row_number + 1)中的@混淆了视图创建的语法，并抛出#1351错误。

CREATE OR REPLACE view vwx (country, name, price, num_row) AS 
    SELECT 
        country, 
        name,    
        price,
        ROW_NUMBER() OVER (ORDER BY final_score) row_num
    FROM 
        testing
    ORDER BY 
        name

CREATE OR REPLACE view vwx (country, name, price, num_row) AS
SELECT country, name, price, @index := @index + 1 AS num_row
FROM testing, (SELECT @index := 0) temp

为查询结果添加行号-无需视图-一次查询

SELECT country, name, price, @ID := @ID + 1 AS row_num
FROM testing,
     (SELECT @ID := 0) temp

将以下内容添加到MySQL的connectionString: Allow User Variables=True;

不要在查询中添加"Cross Join"来更新@ID变量。

警告:试图在视图中使用此命令会导致:View's SELECT contains a variable or parameter

相关内容

最新更新

热门标签：