mysql排序表并获取行位置 [英] mysql sort table and get row position

问题描述

我在mysql中遇到问题，我有一个ID和nubmer表...类似这样的东西

I've got a problem in mysql, I have a table of ID and nubmer ... somthing like that

ID -------- number
3  -------- 340
1  -------- 10
12 -------- 23

现在我想按ID进行选择，比如说ID = 3，有没有办法说出该行在排序表中的位置是什么?在这种情况下，ID = 3将具有第一个位置，以数字的最高值表示. ID = 12将排在第二位……依此类推.

And now I would like to selecet by ID, let's say ID=3 and is there a way of saying what is the position of this row in sorted table? In this case, ID = 3 would have first position, cuase of highest value in number. ID=12 would have second position ... and so on.

推荐答案

set @row_number:=0;
select * from 
  (select ID, @row_number:=@row_number+1 from your_table order by number desc) 
   as row_to_return 
where ID=3;

可以更改上述查询，以将ID替换为您需要的任何内容.
但是，对于简单的用法，ORDER BY number DESC是更好的和经过优化的.

The above query can be change to replace ID to anything you need.
However, for simple usage, ORDER BY number DESC is better and optimized.

这篇关于mysql排序表并获取行位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！