Method of recording line number in the query result set in MySQL

If you need to include a column in the column that the query statement returns that represents the line number in the entire result set, the ISO sql:2003 Standard proposes a method that provides the row_number ()/RANK () function. Standard methods (above 8i versions) can be used in Oracle, and non-standard rownum can be used, while MS SQL Server provides row_number () functions in version 2005, but it does not seem to have such a system self-contained in MySQL. Although LIMIT can easily filter the number and location of the returned result set, the line number of the filtered record cannot be SELECT. It is said that MySQL has long wanted to increase this function, but I haven't found it yet.

The workaround is achieved through predefined user variables:

The code is as follows:
Set @mycnt = 0;
Select (@mycnt: = @mycnt + 1) as RowNum, Othercol from Tblname order by Othercol;

The result set in this query rownum the row number information. The purpose of this line numbering information is that when you need to sort the data according to your needs and take out a row of data after the sort, and you want to know where that row of data is in the previous sort. Like what:

The code is as follows:
Set @mycnt = 0;
SELECT * FROM (
Select (@mycnt: = @mycnt + 1) as rownum, Othercol
From Tblname ORDER by Othercol
As A where Othercol=onekeyid;

Of course, you can also create a temporary table to write the query results to a temporary table with the Auto_increment field to do the query, but in view of the temporary table in the MySQL master/slave mode, the problem may arise, Calculating the row number for each row of a query result set in such a way as a temporary user-defined variable is more concise-unless you want to deal with the entire result set returned in PHP or other language scripts.