select @x:=ifnull(@x,0)+1 as rownum,userid,yestPer from vm_future_performance order by yestPer desc;
查询结果:
f_yestper userid rownum
0.9856 107 1
0.3245 121 2
0.645 130 3
0.7323 111 4
注:如果不用set @x=0;
直接select 会记录上次的rownum值,每次都是在原来基础上增加
php中调用:
$this->db->query("set @x=0;");//先单独执行
$query = $this->db->query("select * from (
select fp.userid userid, fp.name name,
@x:=ifnull(@x,0)+1 as rownum
from vm_future_performance as fp,vm_stock_performance as sp,vm_all_performance ap, vm_user_basic ut
where fp.userid=sp.userid
and ap.userid=fp.userid
and fp.userid=ut.userid
and ut.state = 2
order by ap.todayPer desc,sp.todayPer desc
) tab where userid in(".$userids.")");