MySQL implementation ranking and query the specified user rank function, and rank the function table structure: CREATE table test.testsort (id int (one) not NULL Auto_increment,uid Int (one) DEFAULT 0 COMME NT ' User ID ', score decimal (2) DEFAULT 0.00 COMMENT ' score ', PRIMARY KEY (id)) ENGINE = Innodbauto_increment = 1CHARACTER SET u Tf8collate utf8_general_cicomment = ' Test sort ' Row_format = dynamic; idea: You can sort and then number the results, or you can query the results first, then sort the numbers. Description: @rownum: = @rownum + 1 : = is the function of the assignment, which means to execute @rownum + 1 First, then assign the value to @rownum; (SELECT @rownum: = 0) r This sentence means to set The initial value of the RowNum field is 0, which is the number starting with 1. Implementation ranking: Method One: Select T.*, @rownum: = @rownum + 1 as rownumfrom (select @rownum: = 0) R, (SELECT * from Testsort ORDER by Score DESC) as t; Method II: Select T.*, @rownum: = @rownum + 1 as Rownumfrom (SELECT @rownum: = 0) R, Testsort as Torder by T.score desc; Results: View the specified user rank: method One: Select B.* from (select T.*, @rownum: = @rownum + 1 as rownumfrom (SELECT @ RowNum: = 0) R, (SELECT * from Testsort ORDER by score DESC) as T) as b WHERE B.uid = 222; Method Two: Select b.* from (S Elect t.*, @rownum: = @rownum + 1 as Rownumfrom (SELECT @rownum: = 0) R, Testsort as Torder by T.score DESC) as b where b.uid = 222; Result: &N Bsp
Achieve side rank (same score rank same): Selectobj.uid,obj.score,casewhen @rowtotal = Obj.score then@rownumwhen @rowtotal: = Obj.score Then@rownum: [email protected] + 1WHEN @rowtotal = 0 Then@rownum: [email protected] + 1END as Rownumfrom (SELECTUID,SCOREFR Omtestsortorder byscore DESC) as obj, (select @rownum: = 0, @rowtotal: = NULL) r query specified user rank: SELECT total.* from (selectobj . uid,obj.score,casewhen @rowtotal = Obj.score then@rownumwhen @rowtotal: = Obj.score then@rownum: [email protected] + 1WHE N @rowtotal = 0 Then@rownum: [email protected] + 1END as Rownumfrom (Selectuid,scorefromtestsortorder byscore DESC) as obj, (SELECT @rownum: = 0, @rowtotal: = NULL) r) as total WHERE total.uid = 222;
MySQL implementation ranking and query the specified user ranking function, ranking function
