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

The following together to understand how to successfully query MySQL tied ranking and order ranking, I believe that everyone will benefit a lot after reading, the text is not more refined, I hope how to successfully query MySQL tied ranking and order ranking This short content is what you want.

Stand by. Create a table called scores with the following content. Because of the test ranking, so use the simplest structure. idscore199280387460580699 requirements. To obtain a score ranking, tie ranking is required. If two scores are the same, then both scores rank the same. There should be no "gaps" between rankings. The results are as follows. idscorerank199169913872280358034604sql statement select id, score, (select count(distinct(score)) from scores as b where b.score > a.score ) + 1 as rank from scores as a order by rank; first take out the results, and then analyze how to think about the problem. I don't have capital words for the query keywords here, personal habits! It should be capitalized. Analysis. According to the above requirements, we can know that we are going to do a function of querying by score, just to add a desired ranking to the sorted results. We thought, if we want to know where a certain score ranks, we just need to know how many are higher than it. If there is zero greater than it, it is first; if there is only one greater than it, it is second. And so on. So let's analyze the SQL statement above. It divides the socres table into two identical tables, table a and table b. Then check the value of rank by subquery. Step 1: select id,score, rank from scores order by rank; we query the information we want, but we don't have the rank field in the scores table, so we have to divide it into two ones Like the table, do sub-query, to check the rank. Step 2: select id,score,(select count(score) from scores as b where b.score > a.score) + 1 as rank from scores as a order by rank; If 0 is greater than a certain score, then it is the first. So we're going to add one to the number of queries. The results were as follows:

We found that the results were not what we expected. Because we haven't gone heavy yet. Two of the bigger ones than 87 are 99, so the rank of 87 is 2+1=3, and we want continuous rankings. So use the distinct keyword to de-emphasize. Step 3: select id, score, (select count(distinct(score)) from scores as b where b.score > a.score ) + 1 as rank from scores as a order by rank;

Order Ranking Expected Results

Order ranking We can query in reverse order according to the score field, but use the msyql variable to do rank. Variables in mysql are followed by variable names with '@'.@ In rowNum php we use $rowNum. In mysql, assignment is done with:=. (select @rowNum :=0) r is to give the variable @rowNum an initial value of 0. This is easy to understand. That is, according to the reverse order of the fields we want to rank, query, and then use mysql variable to add a sequence number to each result. sql sentence select t.id, t.score,@rowNum := @rowNum +1 as rank from (select @rowNum :=0) r, scores as t order by t.score desc ; Result

After reading this article, many readers will definitely want to know more about the relevant content. If you need more industry information, you can pay attention to our industry information column.

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.