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屋！