mysqlの動的な列に行をピボットする方法は？

MySQL: 行データを動的列に変換します

問題の説明

次の 3 つの MySQL テーブルがあると仮定します:

• 製品:

  <code>  id | name
     1 | 产品A
     2 | 产品B</code>

• パートナー:

  <code>  id | name
     1 | 合作伙伴A
     2 | 合作伙伴B</code>

• 販売:

  <code>  partners_id | products_id
              1             2
              2             5
              1             5
              1             3
              1             4
              1             5
              2             2
              2             4
              2             3
              1             1</code>

目標は、sales テーブルの行データを、さまざまな製品を表す列名を持つ動的列に変換することです。期待される出力は次のとおりです:

<code>partner_name | 产品A | 产品B | 产品C | 产品D | 产品E
合作伙伴A              1           1           1           1           2
合作伙伴B              0           1           1           1           1</code>

答え

残念ながら、MySQL には専用の PIVOT 関数がありません。ただし、集計関数と CASE ステートメントを組み合わせて、同様の結果を得ることができます。

SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = '产品A' THEN 1 END) AS 产品A,
  COUNT(CASE WHEN pd.product_name = '产品B' THEN 1 END) AS 产品B,
  COUNT(CASE WHEN pd.product_name = '产品C' THEN 1 END) AS 产品C,
  COUNT(CASE WHEN pd.product_name = '产品D' THEN 1 END) AS 产品D,
  COUNT(CASE WHEN pd.product_name = '产品E' THEN 1 END) AS 产品E
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

動的列変換

未知の数の商品を処理するには、動的な列変換を使用する必要があります。これには、products テーブルの情報を使用して実行時に 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;

このアプローチでは、任意の数の product 列を処理でき、SQL ステートメント自体を変更せずにクエリが products テーブルの変更に確実に適応できるようになります。

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