How to add serial number to query in mysql

In mysql, you can use the SELECT statement to define user variables to add serial numbers to the query results. The syntax is "SELECT field 1, field 2, (@i:=@i 1) AS 'serial number' FROM table name, (SELECT @i:=0) AS itable;".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to add serial numbers to queries in mysql

A common solution is to generate serial numbers by defining user variables

Example: Assume that the database has a student table

There are fields in the table: sid, sname, gender, age

Query the data in the table and add the serial number. The corresponding SQL is:

SELECT sid,sname,gender,age,(@i:=@i+1) AS '序号' 
FROM student,(SELECT @i:=0) AS itable;

or

SET @i=0;
SELECT sid,sname,gender,age,@i:=@i+1 AS '序号' 
FROM student;

The query results are as shown in the figure:

Explanation:

1, (@i:=@i 1) can also be written as @i: =@i 1, parentheses are added for visual clarity.

What it means is: add 1 to variable i and assign it to variable i. After defining a variable, the variable will be incremented every time you query. This variable is not needed every time you execute the query statement to obtain the results. Added

2, (SELECT @i:=0) AS itable, defines user variable i, sets the initial value to 0, and then uses it as a derived table. AS defines the alias of the table.

3. SET @i=0. Define user variable i and assign the initial value to 0.

Related knowledge points:

1. How MySQL defines user variables: select @variable name. In the above SQL statement, the name of the variable Yes i

2. User variable assignment: one is to use the "=" sign directly, and the other is to use the ":=" sign. The difference between

= and :=:

When using the set command to assign values ​​to user variables, both methods can be used,

That is: SET @variable name=xxx Or SET @variable name:=xxx

When using the select statement to assign a value to a user variable, you can only use the ":=" method, because in the select statement, the "=" sign is regarded as a comparison operator . That is: SELECT @variable name:=xxx

①: User variable

②: Derived table

③: AS setting alias

is written in Oracle Yes:

SELECT "sid","sname","gender","age",
ROW_NUMBER() over(order by "sid") AS "序号"
FROM "user";

Recommended learning: mysql video tutorial

The above is the detailed content of How to add serial number to query in mysql. For more information, please follow other related articles on the PHP Chinese website!