MySQL 在 ORDER BY 中获取行位置

2021-11-20 00:00:00 sql mysql analytic-functions

使用以下 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 给其中一个时，它们的位置都为 2，而另一个位置为 3...

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

相关文章