今天工作的时候遇见一个需求是根据用户的id对总分数进行排序
刚开始的时候对排序不知怎么写后来发现可以使用数据库中的@来定义一个变量来进行解决
sql语句
一条sql语句解决(注意:((select @count:=0) as t) 括号里边的内容必须定义别名)
select sum(j.job_actual_score) score,t.icon,t.user_name,@count :=@count+1 as serRanking from ((select @count:=0) as t),t_user t join t_job j
where t.uid=j.uid and t.uid=2
GROUP BY t.uid
order by score DESC
也可以使用下面方法来写
set @count=0;
select sum(j.job_actual_score) score,t.icon,t.user_name,@count :=@count+1 as serRanking from t_user t join t_job j
where t.uid=j.uid and t.uid=2
GROUP BY t.uid
order by score DESC