如何高效地检索 MySQL ORDER BY 子句中的行位置？

在MySQL ORDER BY子句中高效获取行位置

数据库表中的数据排序提供了记录的结构化视图。但是，有时需要确定排序结果中特定行的位置。本文探讨如何在MySQL中使用ORDER BY子句实现此目的，并提供一个优化的解决方案，用于检索单行及其位置。

问题陈述：

考虑一个名为“tbl”的MySQL表，它包含两列：“id”和“name”。当按“name”列升序排序时，表数据如下所示：

<code>+-----------+--------------------+
| id         | name               |
+-----------+--------------------+
| 5          | Alpha              |
| 7          | Beta               |
| 3          | Delta              |
| ...        | ...                |
| 1          | Zed                |
+-----------+--------------------+</code>

目标：

目标是从排序表中检索“Beta”行及其在结果集中的位置。所需输出应为：

<code>+-----------+-------------+--------------+
| id         | position    | name        |
+-----------+-------------+--------------+
| 7          |             2 | Beta        |
+-----------+-------------+--------------+</code>

方案一：优化后的查询

为了在ORDER BY子句中检索特定行的位置，我们可以使用子查询根据排序顺序递增地分配行号。以下查询有效地实现了这一点：

<code class="language-sql">SELECT x.id, 
       x.position,
       x.name
  FROM (SELECT t.id,
               t.name,
               @rownum := @rownum + 1 AS position
          FROM tbl t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.name) x
 WHERE x.name = 'Beta'</code>

在此查询中，我们创建了一个子查询（用括号括起来），它使用用户定义的变量（@rownum）来分配递增的行号。与初始化为@rownum := 0的虚拟表进行JOIN，确保数字从1开始。WHERE子句过滤结果，仅检索名称为“Beta”的行。

方案二：替代查询

可以制定以下替代查询来获取位置：

<code class="language-sql">SELECT t.id,
       (SELECT COUNT(*)
          FROM tbl x
         WHERE x.name < t.name) + 1 AS position,
       t.name
  FROM tbl t
 WHERE t.name = 'Beta';</code>

与方案一不同，此查询通过计算名称相同或低于目标名称的行数来计算位置。但是，需要注意的是，这种方法可能会导致名称相同的行出现重复，为它们分配相同的位置值。

以上是如何高效地检索 MySQL ORDER BY 子句中的行位置？的详细内容。更多信息请关注PHP中文网其他相关文章！