Sort by score

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: http://www.idotnet.org/down/mssql.doc