MySQLでカンマ区切り値の列を列に変換する方法

MySQLでカンマ区切り値の列を列に変換する方法

序文

場合によっては、第 1 正規形設計パターンに従わないビジネス テーブルに遭遇することがあります。つまり、複数の属性値が 1 つの列に格納されます。次の表に示すように

ペック価値
1 ET、AT
2 AT、BT
3 AT、DT
4 DT、CT、AT

一般的には2つの共通の要件があります(テストデータについては記事の最後を参照してください)

1. 繰り返しのない値をすべて取得する。

価値
BT
CT
DT
ET

SQL は次のとおりです。

選択distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1))
から
 (select group_concat(distinct `value`) as col from `row_to_col`) として
参加する
 mysql.help_topicをbとして
b.help_topic_id で < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

2. 各値とそれに対応する主キーを表示します。

ペック価値
1 ET
1
2
2 BT
3
3 DT
4 DT
4 CT
4

SQL は次のとおりです。

a.pk、substring_index(substring_index(a.col、'、'、b.help_topic_id+1)、'、'、-1) を選択します。
から
 (`row_to_col` から `value` を col,pk として選択)
参加する
 mysql.help_topicをbとして
b.help_topic_id で < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

実装のアイデア:

要件 1:

1. group_concat関数を使用して値列の値をコンマ区切りの文字列に連結し、substring_index関数を使用して文字列をインターセプトします。
2. substring_index関数の機能を通じて、文字列にカンマがいくつあるか、各カンマの位置を知る必要がある。
3. カンマの数 = char_length(文字列)-char_length(replace(文字列,',',''))
4. カンマの位置 = mysql.help_topic.id < カンマの数 [+1]
5. 最後に、distinct関数を使用して、インターセプトされた単一の値から重複を削除します。

知らせ:
1. mysql.help_topic テーブルの自動増分 ID は 0 から始まるため、インターセプト時に ID を 1 増やす必要があります。参照: substring_index(a.col,',',b.help_topic_id+1)
2. 値列の最後の文字がカンマでない場合: カンマの数 + 1 は、最後のカンマの後の値が切り捨て中に失われないようにするためのものです。つまり、char_length(a.col) - char_length(replace(a.col,',',''))+1;
値列の最後の文字がカンマの場合: カンマの数は +1 である必要はなく、次のようになります: char_length(a.col) - char_length(replace(a.col,',',''))
3. インターセプト時に ID が +1 である必要があるため、接続は <= ではなく < になります。参照: b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))[+1])
4. mysql.help_topic (mysql バージョン: 5.7.21-1) テーブルの自動インクリメント ID、最大値は 636 です。 group_concat の後の文字列内のカンマの数がこの値より大きい場合は、自動インクリメント ID の値を個別に処理する必要があります。

要件2: 最終的なクエリが異なる点を除けば、考え方は基本的に要件1と同じです。

関連する機能:

length: 文字列が占めるバイト数を返します。これは計算フィールドの長さです。漢字または漢字記号は 3 文字としてカウントされ、数字、文字、英語記号は 1 文字としてカウントされます。
char_length: 文字列内の文字数を返します。中国語の文字、数字、文字、記号 (中国語か英語かに関係なく) に関係なく、文字とみなされます。
replace(str,old_string,new_string): 文字列 str 内のすべての old_string を new_string に置き換えます。
substring_index(切り捨てられたフィールド、キーワード、キーワードの出現回数): 文字列を切り捨てます。キーワードの出現回数が負の数の場合は、文字列の末尾まで逆方向にカウントされます。
group_concat([DISTINCT] 接続するフィールド (複数指定可能、カンマ区切り) [ソートフィールド ASC/DESC による順序付け] [区切り文字 'separator']):
group by によって生成された同じグループ内の値を連結し、文字列の結果を返します。デフォルトの区切り文字はカンマです。

テストデータ:

`row_to_col` が存在する場合はテーブルを削除します。
テーブル `row_to_col` を作成します (
 `pk` int(11) NOT NULL AUTO_INCREMENT、
 `value` varchar(255) デフォルト NULL,
 主キー (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=8 デフォルト CHARSET=utf8;

`row_to_col` に VALUES ('1', 'ET,AT') を挿入します。
`row_to_col` に VALUES ('2', 'AT,BT') を挿入します。
`row_to_col` VALUES ('3', 'AT,DT') に INSERT します。
`row_to_col` に VALUES ('4', 'DT,CT,AT') を挿入します。

参照:
https://blog.csdn.net/liuzhoulong/article/details/51729168
https://blog.csdn.net/ldl22847/article/details/47609727

これで、MySQL でカンマ区切りの列を行と列に変換する方法についての記事は終了です。MYSQL のカンマ区切りの列を行と列に変換する方法についての詳細は、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き閲覧してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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

<<:  Element における複数データ読み込み最適化の実装

>>:  Javascript配列の重複排除のいくつかの方法の詳細な説明

推薦する

Docker-compose におけるdepends_on 順序問題を解決する方法についての簡単な説明

コンテナをソートするためにdepends_onを使用しても、コンテナ間の依存関係の問題は完全には解決...

IE6 で CSS スタイルの div または li の背景のタイリングと境界の破損を解決する方法

IE6 で CSS スタイルの div または li の背景のタイリングや境界の破壊を解決するには、...

Centos7 での NFS サービス構築の紹介

目次1. サーバー2. クライアント3. テストサービス1. サーバー1. YUMソースを使用してN...

ドラッグフォトウォールを実現するネイティブJS

この記事では、ネイティブ JS で実装されたドラッグ可能な写真ウォールを紹介します。効果は次のとおり...

HTML フォームとフォーム内部タグの使用

コードをコピーコードは次のとおりです。 <html> <ヘッド> <t...

MYSQLクエリデータの結果に自動的に番号を付ける方法

序文実際、クエリ中に結果に番号が付けられるこのような状況に遭遇したことは一度もありません。同僚が転職...

VirtualBox6上のCentOS7で静的IPを設定する方法と注意点

VirtualBox をインストールした後、CentOS 7 をインストールします。ここでは詳細には...

Vueはv-modelを使用してel-paginationコンポーネントのプロセス全体をカプセル化します。

v-model を使用してページング情報オブジェクトをバインドします。ページング情報オブジェクトに...

Chrome 73 によるフレックスレイアウトの崩れの解析と解決方法

現象プロジェクトにはネストされたフレックス構造がいくつかあります。 <スタイル> /* ...

MySQLのファジークエリの要約

1. 一般的な使用法: (1)%で使用する% は 1 つ以上の文字のワイルドカードを表します。たとえ...

JavaでTomcatサーバーを起動/停止する方法

1. プロジェクト構造 2.Tomcat.javaを呼び出す パッケージ com.calltomca...

MySQL INT型の完全な分析

序文: Integer は MySQL で最もよく使用されるフィールド型の 1 つで、通常は整数を格...

vscodeを使用してuniappを開発する方法

私はフロントエンド プロジェクトの開発に常に vscode を使用してきたため、現在ではいくつかの小...

Linux で MySQL データベースのデータ ファイル パスを変更する手順

rpm インストール方法を使用して MySQL データベースをインストールした後、データ ファイルの...

IE環境では、divの高さはフォントの高さよりも大きくなければならないと規定されています。

コードをコピーコードは次のとおりです。 <div class="content&qu...