MySQLで行を列に変換する方法

MySQL の行から列への操作

いわゆる行から列への操作は、テーブルの行情報を列情報に変換することです。少し大まかすぎるかもしれません。次に例を示します。

+----+-----------+--------+-------+
| ID | ユーザー名 | コース | スコア |
+----+-----------+--------+-------+
| | 張三| 数学| |
| | 張三| 中国語| |
| | 張三| 英語| |
| | 李斯| 数学| |
| | 李思| 中国語| |
| | 李思| 英語| |
| | 王武| 数学| |
| | 王武| 中国語| |
| | 王武| 英語| |
+----+-----------+--------+-------+
 セット内の行数 (0.00 秒)

+-----------+--------+--------+--------+
| ユーザー名 | 数学| 中国語| 英語|
+-----------+--------+--------+--------+
| 張三| | | |
| 李思| | | |
| 王武| | | |
+-----------+--------+--------+--------+
 セット内の行数 (0.00 秒)

上記の例では、表 1 には 3 人の学生の 3 つの成績が表示され、表 2 では表 1 の行レコード情報 (科目、名前) が列情報に変換され、異なる user_name ごとにグループ分けされて表示されます。

1 操作方法の場合

上記の機能を実現するには、分析を実行する必要があります。まず、数学、中国語、英語の 3 つの列を生成し、各列の値に対応するデータを入力する必要があります。ここでは、条件付き操作である MySQL の case when then end 操作を使用する必要があります。この条件文については、まず説明します。

ケース列 
  条件1の場合、結果1
  条件2の場合、結果2
  条件3の場合、結果3
それ以外の場合結果4
終わり

上記の構文は、列の値が条件 1 を満たす場合は、result1 を使用して列の値を置き換える、などと理解できます。列の値が条件を満たさない場合は、result4 を使用して列の値を置き換えます。

それでは実験を始めましょう:

まずテーブルを作成し、次のデータを挿入します。

mysql-yeyz ::>>test_tbl から * を選択します。
+----+-----------+--------+-------+
| ID | ユーザー名 | コース | スコア |
+----+-----------+--------+-------+
| | 張三| 数学| |
| | 張三| 中国語| |
| | 張三| 英語| |
| | 李斯| 数学| |
| | 李思| 中国語| |
| | 李思| 英語| |
| | 王武| 数学| |
| | 王武| 中国語| |
| | 王武| 英語| |
+----+-----------+--------+-------+
 セット内の行数 (0.00 秒)

上記の case when 構文に従って、コースが「数学」の場合、「数学」列を定義し、そのスコアを入力します。「中国語」または「英語」に遭遇した場合は、0 に置き換えます。まず、一般的な次の SQL を記述できます。

mysql-yeyz ::>>ユーザー名を選択、
(CASE course WHEN '数学' THEN score ELSE END ) 数学FROM test_tbl;
+-----------+---------+
| ユーザー名 | 数学 |
+-----------+---------+
| 張三| |
| 張三| |
| 張三| |
| 李思| |
| 李思| |
| 李思| |
| 王武| |
| 王武| |
| 王武| |
+-----------+---------+
 セット内の行数 (0.00 秒)

上記の表には列が 2 つしかないことがわかります。SQL ルールに従って、次のように 'Chinese' と 'English' の値を追加し、さらに複数の列を一度に書き込むことができます。

mysql-yeyz ::>>ユーザー名を選択、
  -> (CASE コース WHEN '数学' THEN スコア ELSE END ) 数学、
  -> (CASE コース WHEN '中国語' THEN スコア ELSE END ) 中国語、
  -> (CASE course WHEN 'English' THEN score ELSE END ) English-> FROM test_tbl;
+-----------+--------+--------+--------+
| ユーザー名 | 数学| 中国語| 英語|
+-----------+--------+--------+--------+
| 張三| | | |
| 張三| | | |
| 張三| | | |
| 李思| | | |
| 李思| | | |
| 李思| | | |
| 王武| | | |
| 王武| | | |
| 王武| | | |
+-----------+--------+--------+--------+
 セット内の行数 (0.00 秒)

これですべての記録が揃い、目標にほぼ到達しました。最終結果との違いを見てみましょう。

+-----------+--------+--------+--------+
| ユーザー名 | 数学| 中国語| 英語|
+-----------+--------+--------+--------+
| 張三| | | |
| 李思| | | |
| 王武| | | |
+-----------+--------+--------+--------+

残っているのは、同じ名前の学生の情報を結合するだけのようです。当然、group_by (user_name) 操作を考えますが、group_by 操作はいくつかの集計関数 (MAX、MIN、AVG、SUM、COUNT など) と組み合わせる必要があります。各レコードには現在の科目のスコアのみが含まれ、他の科目のスコアは0であるため、MAX関数とSUM関数を使用した場合の結果は同じになりますが、AVG関数とMIN関数は使用できません。これは簡単に理解できるはずです。

以下に最終結果を示します。

mysql-yeyz 13:55:52>>ユーザー名を選択、
  -> MAX(CASE コース WHEN '数学' THEN スコア ELSE END ) 数学、
  -> MAX(CASE コース WHEN '中国語' THEN スコア ELSE END ) 中国語、
  -> MAX(CASE コース WHEN '英語' THEN スコア ELSE END ) 英語-> FROM test_tbl
  -> USER_NAME でグループ化;
+-----------+--------+--------+--------+
| ユーザー名 | 数学| 中国語| 英語|
+-----------+--------+--------+--------+
| 張三 | 34 | 58 | 58 |
| 李思 | 45 | 87 | 45 |
| 王武| 76 | 34 | 89 |
+-----------+--------+--------+--------+
セット内の 3 行 (0.00 秒)



mysql-yeyz ::>>ユーザー名を選択、
  -> sum(CASE course WHEN '数学' THEN score ELSE END ) 数学、
  -> sum(CASE course WHEN 'Chinese' THEN score ELSE END ) 中国語、
  -> sum(CASE course WHEN 'English' THEN score ELSE END ) English-> FROM test_tbl
  -> USER_NAME でグループ化;
+-----------+--------+--------+--------+
| ユーザー名 | 数学| 中国語| 英語|
+-----------+--------+--------+--------+
| 張三 | 34 | 58 | 58 |
| 李思 | 45 | 87 | 45 |
| 王武| 76 | 34 | 89 |
+-----------+--------+--------+--------+
セット内の 3 行 (0.00 秒)


mysql-yeyz ::>>ユーザー名を選択、
  -> MIN(CASE コース WHEN '数学' THEN スコア ELSE END ) 数学、
  -> MIN(CASE コース WHEN '中国語' THEN スコア ELSE END ) 中国語、
  -> MIN(CASE コース WHEN '英語' THEN スコア ELSE END ) 英語-> FROM test_tbl
  -> USER_NAME でグループ化;
+-----------+--------+--------+--------+
| ユーザー名 | 数学| 中国語| 英語|
+-----------+--------+--------+--------+
| 張三 | 0 | 0 | 0 |
| 李思| 0 | 0 | 0 |
| 王武| 0 | 0 | 0 |
+-----------+--------+--------+--------+
セット内の 3 行 (0.00 秒)

MAX と SUM を使用した結果は同じであることがわかりますが、集計関数として MIN を使用すると、user_name で指定された対象の最小値である 0 が毎回選択されるため、最終的な出力結果は 0 になります。この結果は簡単に理解できます。

2 操作方法

上記のcase when演算法が理解できれば、if演算法も簡単に理解できます。case whenの構文がifメソッドに変換される点を除けば、原理は同じです。

mysql-yeyz 14:12:42>>ユーザー名を選択、
  -> MAX(if (course= '数学',score,) ) 数学、
  -> MAX(if (course= '中国語',スコア,) ) 中国語,
  -> MAX(if (course= '英語',score,) ) 英語-> FROM test_tbl
  -> USER_NAME でグループ化;
+-----------+--------+--------+--------+
| ユーザー名 | 数学| 中国語| 英語|
+-----------+--------+--------+--------+
| 張三 | 34 | 58 | 58 |
| 李思 | 45 | 87 | 45 |
| 王武| 76 | 34 | 89 |
+-----------+--------+--------+--------+
セット内の 3 行 (0.00 秒)

3 合計列を追加する

基本的な行から列への変換を実装したら、変換されたテーブルに合計フィールドを追加する必要があります。このフィールドは、次のように追加できます。つまり、カウントを開始するときに、スコア値も次のようにカウントします。

mysql-yeyz 14:18:06>>ユーザー名を選択、
  -> (CASE コース WHEN '数学' THEN スコア ELSE END ) 数学、
  -> (CASE コース WHEN '中国語' THEN スコア ELSE END ) 中国語、
  -> (CASE コース WHEN '英語' THEN スコア ELSE END ) 英語、
  -> (スコア) 合計
  -> test_tbl から;
+-----------+---------+---------+--------+-------+
| ユーザー名 | 数学| 中国語| 英語| 合計 |
+-----------+---------+---------+--------+-------+
| 張三 | 34 | 0 | 0 | 34 |
| 張三 | 0 | 58 | 0 | 58 |
| 張三 | 0 | 0 | 58 | 58 |
| 李思 | 45 | 0 | 0 | 45 |
| 李思 | 0 | 87 | 0 | 87 |
| 李思 | 0 | 0 | 45 | 45 |
| 王武 | 76 | 0 | 0 | 76 |
| 王武| 0 | 34 | 0 | 34 |
| 王武| 0 | 0 | 89 | 89 |
+-----------+---------+---------+--------+-------+
セット内の行数は 9 です (0.00 秒)

上記の結果は集計されていません。集計する場合、最初の 3 つの列では sum または max メソッドを使用できますが、最後の列では sum メソッドを使用する必要があります。これは、合計スコアが必要であり、max メソッドを使用すると、値が最高スコアの値になるためです。最終的なSQLは次のようになります。

mysql-yeyz 14:18:29>>ユーザー名を選択、
  -> sum(CASE course WHEN '数学' THEN score ELSE END ) 数学、
  -> sum(CASE course WHEN 'Chinese' THEN score ELSE END ) 中国語、
  -> sum(CASE course WHEN 'English' THEN score ELSE END ) 英語、
  -> 合計(スコア)
  -> test_tbl から
  -> USER_NAME でグループ化;
+-----------+---------+---------+--------+-------+
| ユーザー名 | 数学| 中国語| 英語| 合計 |
+-----------+---------+---------+--------+-------+
| 張三 | 34 | 58 | 58 | 150 |
| 李斯 | 45 | 87 | 45 | 177 |
| 王武 | 76 | 34 | 89 | 199 |
+-----------+---------+---------+--------+-------+
セット内の 3 行 (0.00 秒)

4 シンプルなメソッド group_concat

結果の表示形式にそれほどこだわらない場合は、group_concat 関数という大まかな方法を使用して、すべての列をまとめて 1 つのフィールドに書き込むこともできます。効果は次のようになります。

mysql-yeyz 14:19:13>>ユーザー名を選択、
GROUP_CONCAT(`course`,":",score)AS score FROM test_tbl 
GROUP BY ユーザー名;
+-----------+------------------------------+
| ユーザー名 | 成績 |
+-----------+------------------------------+
| 張三 | 数学: 34、中国語: 58、英語: 58 |
| 李思 | 数学: 45、中国語: 87、英語: 45 |
| 王武 | 数学: 76、中国語: 34、英語: 89 |
+-----------+------------------------------+
セット内の 3 行 (0.00 秒)

この方法は、元のテーブルを直接グループ化するのと同じであり、特定のアプリケーションシナリオにも対応できます。

上記は、MySQL で行を列に変換する方法の詳細な内容です。MySQL の行から列への変換の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: