MySQL在ORDER BY中获得行位置 [英] MySQL get row position in ORDER BY
问题描述
使用以下MySQL表:
With the following MySQL table:
+-----------------------------+
+ id INT UNSIGNED +
+ name VARCHAR(100) +
+-----------------------------+
当按name ASC
排序时,如何选择单行及其在表中其他行中的位置.因此,如果表数据看起来像这样,则按名称排序:
How can I select a single row AND its position amongst the other rows in the table, when sorted by name ASC
. So if the table data looks like this, when sorted by name:
+-----------------------------+
+ id | name +
+-----------------------------+
+ 5 | Alpha +
+ 7 | Beta +
+ 3 | Delta +
+ ..... +
+ 1 | Zed +
+-----------------------------+
如何选择Beta
行以获取该行的当前位置?我要寻找的结果集将是这样的:
How could I select the Beta
row getting the current position of that row? The result set I'm looking for would be something like this:
+-----------------------------+
+ id | position | name +
+-----------------------------+
+ 7 | 2 | Beta +
+-----------------------------+
我可以做一个简单的SELECT * FROM tbl ORDER BY name ASC
然后枚举PHP中的行,但是仅为单个行加载可能较大的结果集似乎很浪费.
I can do a simple SELECT * FROM tbl ORDER BY name ASC
then enumerate the rows in PHP, but it seems wasteful to load a potentially large resultset just for a single row.
推荐答案
使用此:
SELECT x.id,
x.position,
x.name
FROM (SELECT t.id,
t.name,
@rownum := @rownum + 1 AS position
FROM TABLE t
JOIN (SELECT @rownum := 0) r
ORDER BY t.name) x
WHERE x.name = 'Beta'
...以获得唯一的位置值.这个:
...to get a unique position value. This:
SELECT t.id,
(SELECT COUNT(*)
FROM TABLE x
WHERE x.name <= t.name) AS position,
t.name
FROM TABLE t
WHERE t.name = 'Beta'
...将赋予领带相同的值. IE:如果第二个位置有两个值,则当第一个查询将其中两个位置值分别设置为2时,另一个位置的值均设置为3时,它们的位置都将为2.
...will give ties the same value. IE: If there are two values at second place, they'll both have a position of 2 when the first query will give a position of 2 to one of them, and 3 to the other...
这篇关于MySQL在ORDER BY中获得行位置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!