MySQL RowNumber SQL generated from growth serial number

MySQL almost simulates most functions of a commercial database such as Oracle,sql server, functions. Unfortunately, the current version (5.1.33) has not yet implemented the RowNum feature.

Here are some specific implementation methods.

The establishment of the experimental environment is as follows

The code is as follows

Copy Code

Mysql> CREATE TABLE TBL ( -> ID int primary KEY, -> Col int ->); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO TBL values -> (1,26), -> (2,46), -> (3,35), -> (4,68), -> (5,93), -> (6,92); Query OK, 6 rows affected (0.05 sec) Records:6 duplicates:0 warnings:0 Mysql> Mysql> SELECT * from tbl order by Col; +----+------+ | ID | Col | +----+------+ |   1 | 26 | |   3 | 35 | |   2 | 46 | |   4 | 68 | |   6 | 92 | |   5 | 93 | +----+------+ 6 rows in Set (0.00 sec)

1. Directly in the process of implementation;
This should be regarded as one of the most efficient and extremely convenient. Directly in your development program (php/asp/c/...). And so on, directly initializes a variable nrownum=0, and then in the while Recordset, nrownum++; Then the output can be.

2. Use MySQL variables; in some cases, you can consider this method when you cannot modify the program to implement it.
Disadvantage, the @x variable is connection level and needs to be initialized when querying again. Generally speaking, PHP and other B/s applications do not have this problem. However, C/s If the connection one to keep the set @x=0 to consider

The code is as follows	Copy Code
Mysql> Select @x:=ifnull (@x,0) +1 as Rownum,id,col -> from TBL -> Order by Col; +--------+----+------+ | rownum | ID | Col | +--------+----+------+ |  1 |   1 | 26 | |  1 |   3 | 35 | |  1 |   2 | 46 | |  1 |   4 | 68 | |  1 |   6 | 92 | |  1 |   5 | 93 | +--------+----+------+ 6 rows in Set (0.00 sec)

3. Use join query (Cartesian product)
Shortcomings, it is obvious that the efficiency will be worse.

Using the table's self-join, the code is as follows, you can try the select a.*,b.* from tbl A,tbl b where a.col>=b.col to understand the principle of this method.

code is as follows	copy code
Mysql> Select A.id,a.col,count (*) as RowNum -> from TBL A,tbl b -> where A.col>=b.col -> GROUP BY A.id,a.col; +----+------+--------+ | ID | Col | rownum | +----+------+--------+ |   1 |      26 | 1 | |   2 |      46 | 3 | |   3 |      35 | 2 | |   4 |      68 | 4 | |   5 |      93 | 6 | |   6 |      92 | 5 | +----+------+--------+ 6 rows in Set (0.00 sec)

4. Sub-query

Disadvantages, as with join queries, the specific efficiency depends on the configuration of the index and the results of the MySQL optimization.

The code is as follows	Copy Code
Mysql> Select A.*, -> (SELECT COUNT (*) from TBL where Col<=a.col) as RowNum -> from TBL A; +----+------+--------+ | ID | Col | rownum | +----+------+--------+ |   1 |      26 | 1 | |   2 |      46 | 3 | |   3 |      35 | 2 | |   4 |      68 | 4 | |   5 |      93 | 6 | |   6 |      92 | 5 | +----+------+--------+ 6 rows in Set (0.06 sec)