Mysql排序获取排名的实例代码
16lz
2021-12-27
代码如下所示:
SELECT @i:=@i+1 rowNum,if(@total=t.s_score,@rank,@rank:=@i) rank,@total:=t.s_score,t.*from(select t1.* ,t2.s_score from student t1 LEFT JOIN score t2 on t1.s_id=t2.s_id and t2.c_id="01" ORDER BY t2.s_score desc)t,(select @i:=0,@rank:=0,@total:=null) s ;SELECT @i:=@i+1 rowNum,if(@total=t.s_score,@rank,@rank:=@rank+1) rank,@total:=t.s_score,t.*from(select t1.* ,t2.s_score from student t1 LEFT JOIN score t2 on t1.s_id=t2.s_id and t2.c_id="01" ORDER BY t2.s_score desc)t,(select @i:=0,@rank:=0,@total:=null) s ;
其实就是输出mysql的排序后的行号
RT:获取单个用户的成绩在所有用户成绩中的排名
可以分两步:
1、查出所有用户和他们的成绩排名
select id,maxScore,(@rowNum:=@rowNum+1) as rowNo from t_user, (select (@rowNum :=0) ) b order by t_user.maxScore desc
select u.rowNo from ( select id,(@rowNum:=@rowNum+1) as rowNo from t_user, (select (@rowNum :=0) ) b order by t_user.maxScore desc ) u where u.id="2015091810371700001";
更多相关文章
- 《Android和PHP最佳实践》官方站
- android EditText设置不可写
- android 使用html5作布局文件: webview跟javascript交互
- android studio调试c/c++代码
- android用户界面之按钮(Button)教程实例汇
- IM-A820L限制GSM,WCDMA上网的原理(其他泛泰机型可参考)7.13
- 锁屏界面
- android(NDK+JNI)---Eclipse+CDT+gdb调试android ndk程序
- Android(安卓)version and Linux Kernel version