在 MYSQL 查询中获取行位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7057772/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get row position in MYSQL query
提问by Jake
Possible Duplicate:
ROW_NUMBER() in MySQL
Is this possible? I want to get a bunch of results in a specific order and then find out the position of a row by its id, for example:
这可能吗？我想以特定顺序获得一堆结果，然后通过其 id 找出一行的位置，例如：
1, foo
2, bar
3, foobar
I want to get the position of 1 in Alphabetical order, it would return 2
我想按字母顺序获得 1 的位置，它会返回 2
回答by potNPan
SELECT id, name, rank FROM
(
SELECT t.id, t.name,
@rownum := @rownum + 1 AS rank
FROM TABLE t, (SELECT @rownum := 0) r
ORDER BY name ASC
) `selection` WHERE id=1
Modified from this answer >> ROW_NUMBER() in MySQL
从此答案修改>> ROW_NUMBER() in MySQL
回答by Robin Winslow
Working answer (in MySQL):
工作答案（在 MySQL 中）：
If you have the following table "names":
如果您有下表“名称”：
+------+--------+
| id | name |
+------+--------+
| 1 | foo |
| 2 | bar |
| 3 | foobar |
+------+--------+
And you want to know where "foo" ranks alphabetically, then:
并且您想知道“foo”按字母顺序排列的位置，然后：
SELECT z.rank FROM (
SELECT t.id, t.name, @rownum := @rownum + 1 AS rank
FROM names t, (SELECT @rownum := 0) r
ORDER BY name ASC
) as z WHERE id=1;
Will produce:
将产生：
+------+
| rank |
+------+
| 2 |
+------+
The changes from @potNpan's solution are the addition of as zand the change from @rownum = @rownum + 1to @rownum := @rownum + 1. Now it works :)
@potNpan 的解决方案的变化是添加as z和从@rownum = @rownum + 1到的变化@rownum := @rownum + 1。现在它起作用了:)
