关于mysql表数据行列转换方法的讲解-tutorial mysql-php.cn

关于mysql表数据行列转换方法的讲解

jacklove

Lepaskan： 2018-06-08 16:36:08

asal

1968 orang telah melayarinya

开发过程中，因为历史原因或性能原因，需要对表的列数据转为行数据，或行数据转换为列数据使用，本文将介绍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     |
+-------------+--------+

Salin selepas log masuk

行转列后，期望得到以下结果

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

Salin selepas log masuk

行转列，可以使用group_concat()函数结合group by实现。

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               |
+-------------+---------------------+

Salin selepas log masuk

2.列转行

创建测试数据表及数据

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               |
+-------------+---------------------+

Salin selepas log masuk

列转行后，期望得到以下结果

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

Salin selepas log masuk

列转行比行转列复杂，对于列内容是用分隔符分隔的数据，我们可以使用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;

Salin selepas log masuk

执行结果：

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     |
+-------------+--------+

Salin selepas log masuk

本篇讲解了mysql表数据行列转换方法，更多相关内容请关注潘合平中文网。