MySQL で複数の列に基づいて行を列に動的に変換する方法

MySQL の複数の列に基づく行から列への動的変換

前の質問では、MySQL クエリを使用して動的に行を変換しました。単一の列の場合は列に。ここで、「data」と「price」の 2 つの列で同じ結果を達成することを目指します。

問題:

次のテーブルのデータを列に変換します。 「order」と「item」に基づいて、「order1」、「order2」、「order3」、「item1」、「item2」、「item3」、および「item4」という名前が付けられます。

id	order	data	item	Price
1	1	P	1	50
1	1	P	2	60
1	1	P	3	70
1	2	Q	1	50
1	2	Q	2	60
1	2	Q	3	70
2	1	P	1	50
2	1	P	2	60
2	1	P	4	80
2	3	S	1	50
2	3	S	2	60
2	3	S	4	80

望ましい結果:

id	order1	order2	order3	item1	item2	item3	item4
1	P	Q		50	60	70
2	P		S	50	60		80

解決策:

一方、ハードコーディングされたクエリは可能です。 「order」と「item」の値の数がわかっている場合に使用されますが、一般的にはより動的なソリューションが必要です。 case.

データのアンピボット:

MySQL にはアンピボット機能がありませんが、UNION ALL を使用して複数のデータ ペアを行に変換できます:

select id, concat('order', `order`) col,  data value
from tableA
union all
select id, concat('item', item) col, price value
from tableA;

値を変換して戻す列:

その後、CASE:

select id, 
  max(case when col = 'order1' then value end) order1,
  max(case when col = 'order2' then value end) order2,
  max(case when col = 'order3' then value end) order3,
  max(case when col = 'item1' then value end) item1,
  max(case when col = 'item2' then value end) item2,
  max(case when col = 'item3' then value end) item3
from
(
  select id, concat('order', `order`) col,  data value
  from tableA
  union all
  select id, concat('item', item) col, price value
  from tableA
) d
group by id;

動的プリペアド ステートメント:

を使用して、ピボットされていないデータを列に変換し直すことができます。

最後に、セットベースの連結を使用して、クエリを動的に準備されたステートメントに変換できます。 EXECUTE:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when col = ''',
      col,
      ''' then value end) as `', 
      col, '`')
  ) INTO @sql
FROM
(
  select concat('order', `order`) col
  from tableA
  union all
  select concat('item', `item`) col
  from tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  from
                  (
                    select id, concat(''order'', `order`) col,  data value
                    from tableA
                    union all
                    select id, concat(''item'', item) col, price value
                    from tableA
                  ) d
                  group by id');

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

このアプローチは、MySQL の複数の基準に基づいて行を列に変換する柔軟かつ動的な方法を提供します。

以上がMySQL で複数の列に基づいて行を列に動的に変換する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。