mysql行号order by,MySQL在ORDER BY中获得行位置

With the following MySQL table:

+-----------------------------+

+ id INT UNSIGNED +

+ name VARCHAR(100) +

+-----------------------------+

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 +

+-----------------------------+

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 +

+-----------------------------+

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.

解决方案

Use this:

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'

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