Home > Database > Mysql Tutorial > body text

How to add serial number to query in mysql

WBOY
Release: 2022-03-09 15:33:51
Original
25598 people have browsed it

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;".

How to add serial number to query in mysql

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;
Copy after login

or

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

The query results are as shown in the figure:

How to add serial number to query in mysql

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";
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template