How to automatically number the results of MYSQL query data

Preface

In fact, I have never encountered this kind of situation where the results are numbered during a query before. It was only when my colleague was planning to change jobs and asked about this situation during the interview that I thought of studying it. The following is an analysis of a single table query as an example:

SQL:

SELECT (@i:=@i+1) i,user_id,user_name FROM `dt_user_all_orders`, (SELECT @i:=0) as i WHERE user_name='qqqqqqqqqqq' LIMIT 0,10;

result:

If you need to group and then display the sequence number:

SELECT drug_productor,@y:=@y+1 as num FROM( SELECT drug_productor FROM ts_drug a GROUP BY drug_productor) c,(SELECT @y:=0) d

result:

analyze:

At the beginning, a variable i is defined, and each increment of it results in ➕1, @i:=1;

Here we review the way MySQL defines user variables: select @ variable name

There are two ways to assign values ​​to user variables, one is to use the "=" sign directly, and the other is to use the ":=" sign. The difference is that when using the set command to assign values ​​to user variables, both methods can be used; when using the select statement to assign values ​​to user variables, only the ":=" method can be used, because in the select statement, the "=" sign is regarded as a comparison operator.

(@i:=@i+1) can also be written as @i:=@i+1 . The brackets are added to make the structure clearer visually. After defining a variable, it will be incremented for each query. However, we do not need to increment the variable each time we execute a query statement to obtain results, so we need to reset it to 0. We can use (SELECT @i:=0) as i after the table name with a comma. The reason why as i is used in this way is that the derived table must have an alias. This is its alias, which can be any character.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.