ネイティブ PIVOT 関数を使用せずに MySQL でデータを動的にピボットするにはどうすればよいですか?

MySQL の動的ピボット

リレーショナル データベースには、製品の部品に関する情報を保持する「Parts」というテーブルがあります。パーツ ID、パーツ タイプ、および関連する製品 ID。テーブル構造は次のようになります。

Parts
--------------------------------------
part_id | part_type | product_id
--------------------------------------
1        | A        | 1
2        | B        | 1
3        | A        | 2
4        | B        | 2
5        | A        | 3
6        | B        | 3

目的:

私たちの目標は、このデータをピボットされた形式に変換してテーブルを作成するクエリを構築することです。一意の製品 ID ごとに 1 行があり、列は「A」および「B」タイプの対応する部品 ID を表します。目的の出力テーブルは次のようになります。

Product_ID | Part_A_ID | Part _B_ID
----------------------------------------
1           | 1         | 2
2           | 3         | 4
3           | 5         | 6

解決策:

残念ながら、MySQL にはネイティブ PIVOT 関数がありません。ただし、集計関数と CASE ステートメントを組み合わせて使用​​すると、その動作をシミュレートできます。複数のパーツ タイプを処理する動的バージョンを作成するには、準備済みステートメントを使用します。

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;

このクエリは、「Parts」テーブル内の固有のパーツ タイプに基づいて SQL ステートメントを動的に構築します。次に、準備されたステートメントを使用して生成された SQL ステートメントを実行し、大規模な結果セットによる潜在的なパフォーマンスの問題を効率的に処理します。

列数が制限された静的ソリューションの代替:

部品タイプの数が限られている場合は、静的バージョンのクエリを使用できます。

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

この代替案は次のとおりです。部品種類が決まっていて少ない場合におすすめです。

以上がネイティブ PIVOT 関数を使用せずに MySQL でデータを動的にピボットするにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。