MySQL ORDER BY 句で行の位置を効率的に取得する方法

MySQL ORDER BY 句で行の位置を効率的に取得します

データベース テーブル内のデータを並べ替えると、レコードの構造化されたビューが提供されます。ただし、場合によっては、並べ替え結果内の特定の行の位置を決定する必要があります。この記事では、MySQL の ORDER BY 句を使用してこれを実現する方法を検討し、単一行とその場所を取得するための最適化されたソリューションを提供します。

問題ステートメント:

「id」と「name」の 2 つの列を含む「tbl」という名前の MySQL テーブルについて考えてみましょう。 「名前」列で昇順にソートすると、テーブルのデータは次のようになります:

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

ターゲット:

目標は、ソートされたテーブルから「ベータ」行と結果セット内のその位置を取得することです。望ましい出力は次のようになります:

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

オプション 1: 最適化されたクエリ

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」という名前の行のみを取得します。

オプション 2: 代替クエリ

次の代替クエリを作成して位置を取得できます:

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

シナリオ 1 とは異なり、このクエリは、ターゲット名と同じ名前、またはターゲット名よりも小さい名前を持つ行の数を数えることによって位置を計算します。ただし、この方法では同じ名前の行が重複し、同じ位置の値が割り当てられる可能性があることに注意することが重要です。

以上がMySQL ORDER BY 句で行の位置を効率的に取得する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。