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

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 の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL の行から列の詳細
  • MySQLでカンマ区切り値の列を列に変換する方法
  • MySQL で行を列に変換したり、列を行に変換したりする詳細な例
  • MySQL ストアド プロシージャで動的な行から列への変換を使用する
  • MySQL の行から列へ、列から行へ

<<:  JS にこれがあるのはなぜですか?

>>:  加算、減算、乗算、除算の機能を実現するには、HTML に 2 つの数値を入力します。

推薦する

JavaScriptの強力な演算子をいくつか見てみましょう

目次序文1. ヌル合体演算子2. ??= 空代入演算子3. ?. オプション連鎖演算子4. ?: 三...

JS上級編ES6の6つの継承方法

目次1. プロトタイプチェーン継承2. コンストラクタによる継承3. 組み合わせ継承4. プロトタイ...

Linux の fsevents モジュールによって発生する npm ls エラーの解決方法の詳細な説明

Mac で開発されたプロジェクトがあり、パッケージ npm i がインストールされており、すべて正常...

CSS ハート型読み込みアニメーションのソースコードの実装

さっそく、コードをお見せしましょう。コードは非常にシンプルなので、勉強すれば理解できるようになります...

知っておくべき JS 配列削減の高度な使い方 25 選

序文Reduce は ES5 で追加された新しい従来の配列メソッドの 1 つです。forEach、f...

ボタンをクリックしてテキストを入力ボックスに変換し、保存をクリックしてテキスト実装コードに変換します。

ボタンをクリックしてテキストを入力ボックスに変換し、保存をクリックしてテキスト実装コードに変換します...

Linux での MySQL 5.7.18 yum のアンインストールからインストールまでのプロセスの図

いろいろ苦労しましたが、やっと yum インストールの手順がわかりました。以前、バイナリ パッケージ...

画像のプリロードと遅延ロードを実装するJavaScript

この記事では、JavaScriptで画像のプリロードと遅延ロードを実装するための具体的なコードを参考...

HTML 要素に注釈を付けるときにクラスと ID のどちらが優れているかを分析する

Web ページには、非常に複雑な HTML 構造があります。CSS を使用して関連するスタイルを定義...

ブラウザのキャッシュを防ぐために、js または css の後に ?v= バージョン番号を追加します。

コードをコピーコードは次のとおりです。 <span style="font-size...

MySQL マルチテーブル結合クエリ例の説明

実際のプロジェクトでは、複数のテーブル間に関係が存在します。 1 つのテーブル内のすべてのデータを取...

MySQLのexplain型の詳細な説明

導入:多くの場合、さまざまな選択ステートメントを使用して必要なデータを照会した後、多くの人は作業が正...

dockerコンテナにviコマンドをインストールする簡単な操作

docker コンテナを使用する場合、vim がインストールされていないことがあり、vim コマンド...

CentOS に MySQL をインストールしてリモート アクセスを設定する方法

1. MySQLリポジトリソースをダウンロードする$ wget http://repo.mysql....