mysql中两种获取ranking(排序)的方法

//当发生积分变动式,通过 UPDATE SET 字句 + ORDER BY字句显式修改
\Zhuanti\ExpoUser::rawExecute('SET @rowNum=0');
 $sql = 'UPDATE '.$this->expoUser->getSource().' SET ranking=(@rowNum:=@rowNum+1) ORDER BY integral_total DESC,scan_time ASC,id ASC';
\Zhuanti\ExpoUser::rawExecute($sql);
$integral_total = $this->expoUser->integral_total;
$integral_over =  $this->expoUser->integral_over;

//2.在查询的方法中
//获取到相关的用户信息再通过子查询的方式获得名次
if(false === $this->expoUser)
		{
			exit('参数错误');
		}
		else
		{
			$sql = 'SELECT rank FROM(SELECT @row := @row +1 AS rank,integral_total,
                    user_id,scan_time FROM (SELECT u.id ,u.user_id,
				   u.integral_total,u.scan_time from '.$expoUser->getSchema().
                    '.'.$expoUser->getSource().' AS u WHERE
				   u.integral_total >= '.$expoUser->integral_total.'
                   ORDER BY integral_total DESC,scan_time ASC) t,(SELECT @row:=0) tt )ss';
			$sql.=' WHERE user_id='.$this->auth->user_id;
			$UserRank = \Zhuanti\ExpoUser::rawFind($sql);
			$UserRank = $UserRank->toArray();
			if(isset($UserRank[0]))
			{
				$rank = $UserRank[0]['rank'];
			}
			$expoUser = $this->expoUser->toArray();
}

 

转载于:https://my.oschina.net/leolee2356/blog/791451