How to add sequence number to query by mysql

In this article, the editor introduces in detail "mysql how to add the serial number to the query", the content is detailed, the steps are clear, and the details are handled properly. I hope this "mysql how to add the serial number to the query" article can help you solve your doubts.

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

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

How does mysql number a query?

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

Example: suppose the database has a student table

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 'serial number' FROM student, (SELECT @ iRank 0) AS itable

Or

SET @ iPre0nent select sid,sname,gender,age,@i:=@i+1 AS 'serial number' FROM student

The query result is shown in the figure:

Explanation:

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

It means that the variable I plus 1 is assigned to the variable I. after defining a variable, every query will augment the variable, and every time the query statement is executed to get the result, there is no need for this variable to augment itself.

2. (SELECT @ iAS _ 0) AS itable, define the user variable I, set the initial value to 0, then use it as a derived table, and AS defines the alias for the table.

3. SET @ iTun0. Define the user variable I, with an initial value of 0

Related knowledge points:

1. How MySQL defines user variables: select @ variable name. In the above SQL statement, the variable name is 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 you use the set command to assign values to user variables, you can use both methods

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

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

①: user variabl

②: derived tabl

③: AS setting alias

It is written in Oracle as follows:

SELECT "sid", "sname", "gender", "age", ROW_NUMBER () over (order by "sid") AS "serial number" FROM "user"; read here, this "mysql how to add serial number to the query" article has been introduced, want to master the knowledge of this article also need to practice and use in order to understand, if you want to know more about the article, welcome to pay attention to the industry information channel.