Shulou(Shulou.com)06/01 Report--

Preface

As a matter of fact, I have never encountered this situation of numbering the results during a query before. My colleague is going to change jobs. I only thought of studying this situation when I came back from the interview. Take a single-table query as an example:

SQL:

SELECT (@ i:=@i+1) I username FROM `username orders`, (SELECT @ iRut0) as i WHERE user_name='qqqqqqqqqq' LIMIT 0Magi 10

Results:

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

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

Results:

Analysis:

At the beginning, you define a variable I so that the result of each increment is ➕ 1, @ iVERV1.

Here, by the way, review how 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 both methods can be used when using the set command to assign user variables; when using select statements to assign 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, and parentheses are added to make the structure clearer visually. After defining a variable, each query will augment the variable, and every time we execute the query statement to get the result, we do not need this variable to augment itself, so we need to reset it to 0 and use (SELECT @ ias _ 0) as I under the cell after the table name. Explain why this as I is used in this way, because the derived table must need an alias, which is its alias. You can have any character.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.