如何有效率地檢索 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子句中檢索特定行的位置，我們可以使用子查詢根據排序順序遞增地分配行號。以下查詢有效地實現了這一點：

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'

在此查詢中，我們建立了一個子查詢（用括號括起來），它使用使用者定義的變數（@rownum）來指派遞增的行號。與初始化為@rownum := 0的虛擬表進行JOIN，確保數字從1開始。 WHERE子句過濾結果，僅檢索名稱為「Beta」的行。

方案二：替代查詢

可以製定以下替代查詢來取得位置：

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';

與方案一不同，此查詢透過計算名稱相同或低於目標名稱的行數來計算位置。但是，需要注意的是，這種方法可能會導致名稱相同的行出現重複，為它們分配相同的位置值。

以上是如何有效率地檢索 MySQL ORDER BY 子句中的行位置？的詳細內容。更多資訊請關注PHP中文網其他相關文章！