mysqlで行間のピボットの列を動的に生成する方法は？

MySQL の行から列への動的列生成

3 つの MySQL テーブルがあるとします。

• products テーブル: には製品情報 (ID、名前) が格納されます。
• パートナー テーブル: にはパートナー情報 (ID、名前) が格納されます。
• 販売テーブル: は販売トランザクション (partners_id、products_id) を記録します。

私たちの目標は、販売データを、パートナーが行としてリストされ、製品が動的列としてリストされたテーブルに変換することです。

事前集計結果

簡単なクエリを使用して、パートナー名と製品名を列として含むテーブルを、出現数とともに取得できます。

<code class="language-sql">SELECT partners.name AS partner_name, 
       products.name AS product_name, 
       COUNT(*) 
FROM sales
JOIN products ON sales.products_id = products.id
JOIN partners ON sales.partners_id = partners.id
GROUP BY sales.partners_id, sales.products_id</code>

ただし、このアプローチには、変動する製品数量に対する動的な列生成が欠けています。

CASE 関数と集計関数を使用した動的ピボット

MySQL には専用の PIVOT 関数がないため、集計クエリには CASE ステートメントを使用する必要があります。

<code class="language-sql">SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = 'Product A' THEN 1 END) AS ProductA,
  COUNT(CASE WHEN pd.product_name = 'Product B' THEN 1 END) AS ProductB,
  COUNT(CASE WHEN pd.product_name = 'Product C' THEN 1 END) AS ProductC,
  COUNT(CASE WHEN pd.product_name = 'Product D' THEN 1 END) AS ProductD,
  COUNT(CASE WHEN pd.product_name = 'Product E' THEN 1 END) AS ProductE
FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name</code>

このメソッドは、各パートナーの各製品名の出現数を動的にカウントできます。

準備されたステートメントを使用した動的パースペクティブ

真に動的なピボット テーブルの場合、準備されたステートメントを使用して、製品の数に基づいて SQL クエリ文字列を生成できます。

<code class="language-sql">SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN Product_Name = ''',
      Product_Name,
      ''' THEN 1 END) AS ',
      REPLACE(Product_Name, ' ', '')
    )
  ) INTO @sql
FROM products;

SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;</code>

このメソッドを使用すると、各製品の列名と集計数を動的に生成できます。必要に応じて、GROUP_CONCAT のサイズ制限を調整してください。

以上がmysqlで行間のピボットの列を動的に生成する方法は？の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。