从有序查询中查询Mysql

问题描述：

I've done some research and ended up with this query.

SET @row_num = 0;
SELECT
   @row_num := @row_num + 1 as row_number
  ,id
  ,maxPoints
FROM users
ORDER BY maxPoints

...that gives me indexes of table ordered by maxPoints. And now comes the query for smarter people...

How do I get the row_number WHERE id = $i AND ... (other parameters) from this result.

Is it possible to do it by one composed query or do I need to split those queries on php side? I'm not able to put it up syntactically.

我做了一些研究并最终得到了这个查询。 p>

  SET @row_num = 0; 
SELECT 
 @row_num：= @row_num + 1 as row_number 
，id 
，maxPoints 
FROM users 
ORDER BY maxPoints \  n  code>  pre> 
 
  ...它给出了由maxPoints排序的表索引。 现在是对更聪明人的查询......  p> 
如何从此结果中获取row_number WHERE  id  strong> = $ i AND ...（其他参数）。  p> 
 
 
是否可以通过一个组合查询来完成它，还是需要在php端拆分这些查询？ 我无法在语法上提出来。   p> 
  div>

答

Just put it in a subquery and select from that

select row_number
from
(
    SELECT id, @row_num := @row_num + 1 as row_number
    FROM users
    CROSS JOIN (select @row_num := 0) r
    ORDER BY maxPoints
) tmp
where id = $id

BTW you can init the @row_num variable with a subquery too, like I did in my query

从有序查询中查询Mysql

相关推荐