如何在MySQL视图中生成序列号? [英] How to generate sequence number in MySQL view?

问题描述

我正在Linux下使用MySQL 5.6.

I am using MySQL 5.6 under Linux.

我有一张表，可以让用户输入一个从号和一个到号.

I have a table to let user input a from-number and a to-number.

然后，有一个视图可以从另一个表中选择一些记录，其帐号在起始编号和终止编号之间.

Then, there is a view to select some records from another table with account numbers between the from-number and the to-number.

最困难的问题是用户想要视图中每个记录的SEQUENCE编号，从1开始.例如，如果视图显示37行，则序列号应该从1、2、3，...开始.直到37，没有跳跃数.视图的排序顺序并不重要.

The most difficult problem is that user wants a SEQUENCE number for each records in the view, starting from 1. For example, if the view shows 37 rows, the sequence number should starts from 1, 2, 3, ... until 37, no jumping number. The sorting order of the view is not important.

我知道MySQL表中有自动递增列.但是，就我而言，我必须使用VIEW，而不是表格.

I know that there is auto-increment column in MySQL table. But, for my case, I have to use a VIEW, not a table.

有人知道怎么做吗?

顺便说一句，我必须使用VIEW来执行此操作，而不是SELECT语句.用户不知道如何输入SELECT语句，但是他们知道如何单击视图以查看视图.

BTW, I have to use a VIEW to do so, not a SELECT statement. User does not know how to input SELECT statement, but they know how to click the view to look at the view.

推荐答案

顺便说一句，我必须使用VIEW来执行此操作，而不是SELECT语句.用户做 不知道如何输入SELECT语句，但是他们知道如何单击 查看该视图.

BTW, I have to use a VIEW to do so, not a SELECT statement. User does not know how to input SELECT statement, but they know how to click the view to look at the view.

从技术上讲，您想要这样的东西来模拟排名或行号.

Technically you want something like this to simulate ranking or a row number..

CREATE VIEW table_view 
AS
 SELECT
  *
  , (@row_number := @row_number + 1) AS row_number 
 FROM 
  table
 # Because a SQL table is a unsorted set off data ORDER BY is needed to get stabile ordered results.
 ORDER BY
  table.column ASC 
CROSS JOIN (SELECT @row_number := 0) AS init_user_var  

您无法使用此SQL代码，如果尝试使用用户变量创建视图，则会出现以下错误.

You can't use this SQL code you will get the error below if you try to create a View with a user variable.

Error Code: 1351
View's SELECT contains a variable or parameter

下面的SQL代码还可以生成row_number. 假设您有一个id列，该列是用AUTO_INCREMENT生成的. 但是子查询是一个相关子查询，这使得在较大的表上执行非常慢，因为需要对每条记录执行计数.

The SQL code below also makes it possible to generate the row_number. This assumes that you have a id column what is generated with AUTO_INCREMENT. But the subquery is a correlated subquery what makes the execution very slow on larger tables because the counting need to be executed on every record.

CREATE VIEW table_view
AS
 SELECT 
  *
  , (SELECT COUNT(*) + 1 FROM table inner WHERE inner.id < outer.id) AS row_number
 FROM 
   table outer

仅MySQL 8.0 +.

MySQL 8.0+ Only.

MySQL支持窗口功能，因此不需要MySQL的用户变量即可模拟排名或行号.

MySQL supports window functions so no MySQL´s user variables are needed to simulate ranking or a row number.

CREATE VIEW table_view 
AS
 SELECT
  *
 # Because a SQL table is a unsorted set off data ORDER BY is needed to get stabile ordered results.
  , (ROW_NUMBER() OVER (ORDER BY table.column ASC)) AS row_number
 FROM 
  table

这篇关于如何在MySQL视图中生成序列号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！