mysql数据库排名实现

声明三个初始变量

@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0


将@rownum +1 ，赋值给 @rownum

@rownum := @rownum + 1


@rownum := @rownum + 1 AS num_tmp
这一行在下面内容之前，则能实现并列排序。只写这一行的话，输出的排序是顺序排序

@incrnum := CASEWHEN @rowtotal = obj.borrowedNum THEN@incrnumWHEN @rowtotal := obj.borrowedNum THEN@rownumEND AS rownum


SELECTobj.STU_NAME,obj.CONTACT_INFO,obj.borrowedNum,@rownum := @rownum + 1 AS num_tmp,@incrnum := CASEWHEN @rowtotal = obj.borrowedNum THEN@incrnumWHEN @rowtotal := obj.borrowedNum THEN@rownumEND AS rownum
FROM(SELECTstudent.STU_NAME,student.CONTACT_INFO,a.s_c_id borrowedNum,@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0FROMstudent_class aLEFT JOIN student ON a.STU_ID = student.STUDENT_IDWHEREa.CM_ID IN (129381)AND a.SC_STATE IN (119, 120)GROUP BYa.STU_ID HAVING borrowedNum > 0ORDER BYborrowedNum DESC) AS obj