A detailed introduction to ranking functions in mysql

Ranking a certain field in the MySQL data table

1. Original table data As shown below. done_seconds is the completion time, and the requirement is to rank each piece of data based on this field.

##2. Enter the following SQL statement:

SELECT A.*,@rank:=@rank+1 AS pm FROM (SELECT * FROM task_news_user_done_list ORDER BY done_seconds) A, (SELECT  @rank:=0) B

3. After executing this statement, the pm field is added to the query results. The value of this field is the result of sorting based on done_seconds. Note the issue of ascending and descending order.

#4. In addition to individual fields, average values ​​can also be sorted. If you want to sort the average value of done_seconds for the same user_email in the above table, you can use the following sql statement:

SELECT A.*,@rank:=@rank+1 AS pm 
FROM 
(SELECT user_email, AVG(done_seconds) AS done_seconds FROM task_news_user_done_list GROUP BY user_email ORDER BY done_seconds) A, (SELECT  @rank:=0) B

5. The result after sorting is as shown below. The ranking at this time is the average ranking of all done_seconds of the same user_email.

##6. Of course, you can also join as needed where and other restrictions. As shown below.

Please click like if you find it useful.

#

The above is the detailed content of A detailed introduction to ranking functions in mysql. For more information, please follow other related articles on the PHP Chinese website!