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

MySQL variables include system variables and system variables. This learning task is a user-defined variable. User variables mainly include local variables and session variables.

The declaration method of a user-defined variable is like @ var_name, where the variable name consists of letters, numbers, ".", "_", and "$". Of course, you can also include other characters when referencing as a string or identifier (for example: @ 'my-var',@ "my-var", or @ my-var).

User-defined variables are session-level variables. The scope of its variables is limited to the client link that declares it. When this client is disconnected, all of its session variables will be released.

User-defined variables are case-insensitive.

Use the set statement to declare user-defined variables:

SET @ my_var = 1; SET @ my_var: = 1

Use: = assignment when not using set, because using = may be thought of as a comparison operator.

The following is illustrated by a case:

Write a SQL query to achieve score ranking. If the two scores are the same, the two score rankings (Rank) should be the same. Please note that the next ranking after the draw should be the next consecutive integer value. In other words, there should be no "loopholes" between ranks.

+-+

| | Id | Score |

+-+

| | 1 | 3.50 |

| | 2 | 3.65 |

| | 3 | 4.00 |

| | 4 | 3.85 |

| | 5 | 4.00 |

| | 6 | 3.65 | |

+-+

For example, based on the above Scores table, your query should generate the following report (sorted by the highest score):

+-+ +

| | Score | Rank |

+-+ +

| | 4.00 | 1 | |

| | 4.00 | 1 | |

| | 3.85 | 2 | |

| | 3.65 | 3 | |

| | 3.65 | 3 | |

| | 3.50 | 4 | |

+-+ +

Query statement:

Select Score, @ rank: = @ rank + (@ pre (@ pre:=Score)) Rank from Scores, (SELECT @ rank: = 0metallurgical prelude =-1) INIT ORDER BY Score DESC

Note:

@ rank indicates the ranking of scores

@ pre indicates the score of the last person

@ rank= @ rank + 1 when the score is different from the previous score, otherwise, @ rank=rank.

Initialize @ rank to 1 and initialize it to-1.

The experimental results are as follows:

The above detailed explanation of the use of custom parameters in MySQL is all the content shared by the editor to you. I hope I can give you a reference and I hope you can support it.

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.