mysqlテーブルデータの行と列を変換する方法の解説-mysql チュートリアル-php.cn

mysqlテーブルデータの行と列を変換する方法の解説

jacklove

リリース： 2018-06-08 16:36:08

オリジナル

1968 人が閲覧しました

開発過程において、歴史的な理由やパフォーマンス上の理由により、テーブルの列データを行データに変換したり、行データを列データに変換して使用する必要があります。この記事では、行データを変換する方法を紹介します。 mysql テーブルデータの列と、完全なデモンストレーション例と SQL スキルを提供します。

1. 行を列に変換します

テストデータテーブルとデータを作成します

CREATE TABLE `option` ( `category_id` int(10) unsigned NOT NULL COMMENT &#39;分类id&#39;, `name` varchar(20) NOT NULL COMMENT &#39;名称&#39;, KEY `category_id` (`category_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `option` (`category_id`, `name`) VALUES
(1, &#39;大&#39;),
(1, &#39;中&#39;),
(1, &#39;小&#39;),
(2, &#39;奔驰&#39;),
(2, &#39;宝马&#39;),
(3, &#39;2015&#39;),
(3, &#39;2016&#39;),
(3, &#39;2017&#39;),
(3, &#39;2018&#39;),
(4, &#39;1m&#39;),
(4, &#39;2m&#39;);mysql> select * from `option`;
+-------------+--------+| category_id | name   |
+-------------+--------+|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     ||           4 | 2m     |
+-------------+--------+

ログイン後にコピー

行を列に変換すると、次の結果が期待できます

+-------------+---------------------+| category_id | name                |
+-------------+---------------------+|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 ||           4 | 1m,2m               |
+-------------+---------------------+

ログイン後にコピー

行を列に変換するには、group_concat()を使用できます。グループ化を結合する機能を実現しました。

group_concat() 関数は、式の組み合わせの接続値を取得できます。デフォルトの区切り文字はカンマですが、separator を使用して他の区切り文字に設定できます。

注: group_concat() 関数には返される結果の長さ制限がありますが、デフォルトは 1024 バイトですが、通常の状況では十分です。

group_concat()関数の使い方については、以前の記事「mysql関数concatとgroup_concatの使い方」を参照してください

実行結果:

mysql> select category_id,group_concat(name) as name from `option` group by category_id order by category_id;
+-------------+---------------------+| category_id | name                |
+-------------+---------------------+|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 ||           4 | 1m,2m               |
+-------------+---------------------+

ログイン後にコピー

テストデータのテーブルとデータの作成

CREATE TABLE `option2` ( `category_id` int(10) unsigned NOT NULL COMMENT &#39;分类id&#39;, `name` varchar(100) NOT NULL COMMENT &#39;名称集合&#39;) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `option2` (`category_id`, `name`) VALUES
(1, &#39;大,中,小 &#39;),
(2, &#39;奔驰,宝马&#39;),
(3, &#39;2015,2016,2017,2018&#39;),
(4, &#39;1m,2m&#39;);mysql> select * from `option2`;
+-------------+---------------------+| category_id | name                |
+-------------+---------------------+|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 ||           4 | 1m,2m               |
+-------------+---------------------+

ログイン後にコピー

列を行に変換すると、次の結果が期待されます

+-------------+--------+| category_id | name   |
+-------------+--------+|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     ||           4 | 2m     |
+-------------+--------+

ログイン後にコピー

列の内容が区切り文字で区切られているデータの場合、列を行に変換することは、行から列に変換するよりも複雑です。

substring_index()

関数を使用して出力を分割し、デカルト積と組み合わせてループを実装できます。

select a.category_id,substring_index(substring_index(a.name,&#39;,&#39;,b.category_id),&#39;,&#39;,-1) as name from `option2` as ajoin `option2` as b on b.category_id<=(length(a.name) - length(replace(a.name,&#39;,&#39;,&#39;&#39;))+1)order by a.category_id,b.category_id;

ログイン後にコピー

実行結果:

mysql> select a.category_id,substring_index(substring_index(a.name,&#39;,&#39;,b.category_id),&#39;,&#39;,-1) as name from `option2` as a    -> join `option2` as b on b.category_id<=(length(a.name) - length(replace(a.name,&#39;,&#39;,&#39;&#39;))+1)    -> order by a.category_id,b.category_id;
+-------------+--------+| category_id | name   |
+-------------+--------+|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     ||           4 | 2m     |
+-------------+--------+

ログイン後にコピー

この記事では、mysql テーブルデータの行と列を変換する方法について説明します。関連コンテンツの詳細については、Pan Heping 中国語 Web サイトを参照してください。
関連する推奨事項:

php を使用して複数の 1 次元配列を 2 次元配列に結合する方法

php で配列内の指定された複数の列を返す関連メソッドを説明する
php についてRedisカウンタークラスの詳しい説明
以上がmysqlテーブルデータの行と列を変換する方法の解説の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。