準備されたステートメントを使用して MySQL で動的ピボットを実行する方法?

MySQL の動的ピボット

MySQL では、テーブルに正規化された形式のデータが含まれることが多く、より多くのユーザーがデータを取得するには複雑なクエリが必要になります。フレンドリーなフォーマット。このような変換の 1 つは、列が行に変換されるピボットです。

製品部品 (part_id) を製品タイプ (part_type) および製品 ID (product_id) に関連付ける、「Parts」のようなテーブルについて考えてみましょう。このテーブルをピボットして、さまざまなパーツ タイプの各製品のパーツ ID を示す新しいテーブルを作成したいと考えています。

MySQL には専用の PIVOT 関数がないため、代替手法を使用する必要があります。解決策の 1 つは、集計関数と CASE ステートメントを使用することです。動的ピボットの場合、プリペアド ステートメントを活用できます。

動的ピボット クエリ

次のクエリは、動的 SQL を使用して「Parts」テーブルをピボットします:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when part_type = ''',
      part_type,
      ''' then part_id end) AS part_',
      part_type, '_id'
    )
  ) INTO @sql
FROM
  parts;
SET @sql = CONCAT('SELECT product_id, ', @sql, ' 
                  FROM parts 
                   GROUP BY product_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

このクエリは、テーブル内に存在する個別のパーツ タイプに基づいて SQL ステートメントを動的に生成し、実行します。パフォーマンスを向上させるために準備されたステートメントを使用します。

結果の例

結果のテーブルは次の形式になります:

product_id      part_A_id      part_B_id
----------------------------------------
1               1              2
2               3              4
3               5              6

静的ピボット クエリ

限られた数のパーツ タイプの場合、静的ピボット クエリを使用できます:

select product_id,
  max(case when part_type ='A' then part_id end) as Part_A_Id,
  max(case when part_type ='B' then part_id end) as Part_B_Id
from parts
group by product_id

このクエリはパーツ タイプを手動で指定し、ピボット テーブルを生成します。

以上が準備されたステートメントを使用して MySQL で動的ピボットを実行する方法?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。