Sort by score

Source: Internet
Author: User

ID Username Classid Score
1 Aaa 9802 66
2 Bbb 9801 70
3 CCC 9801 69
4 Ddd 9801 88


Set the score to be unique to obtain the score ranking for each username. 2-2.f.1---------------------------------------------------- Select username, (select count (*) from student where score> =. score) when a from student a queries a record, the number of current records is counted from the table, and the score is greater than or equal to (because the score is not repeated, It is equal to itself. therefore, the ranking is obtained.

When a score has a duplicate value, the ranking also has a duplicate value, and there will be a broken ranking number. In this case, you need to take a further step. 2-2.f.2Select distinct score into # T from studentselect. username, B. index from student ainner join (select score, (select count (*) from # t where score> =. score) index from # t a) B on. score = B. scoredrop table # T gets an uninterrupted ranking. take a closer look at the statement. Here, the temporary table # t is not processed, and it only plays a temporary role in data storage. This simplifies the SQL query statement. you can replace # T with the SELECT query statement that generates it, and use an SQL statement to implement this function (if the subquery is used instead of # t, for more information, refer to my description on virtual tables. It is easier to understand that temporary tables are replaced with virtual tables ). (note. we do not encourage you to use an SQL query to complete the query. Instead, we recommend that you do this when learning to improve your proficiency. in the practical application should use a statement to analyze specific issues) Xiang:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.