mysql排序表并获取行位置

本文介绍了mysql排序表并获取行位置的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在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.