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


本文摘自PHP中文网,作者jacklove,侵删。

开发过程中,因为历史原因或性能原因,需要对表的列数据转为行数据,或行数据转换为列数据使用,本文将介绍mysql表数据行列转换的方法,提供完整演示例子及sql技巧。

1.行转列

创建测试数据表及数据

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

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

 

INSERT INTO `option` (`category_id`, `name`) VALUES

(1, '大'),

(1, '中'),

(1, '小'),

(2, '奔驰'),

(2, '宝马'),

(3, '2015'),

(3, '2016'),

(3, '2017'),

(3, '2018'),

(4, '1m'),

(4, '2m');mysql> select * from `option`;

+-------------+--------+| category_id | name   |

+-------------+--------+|           1 | 大     |

|           1 | 中     |

|           1 | 小     |

|           2 | 奔驰   |

|           2 | 宝马   |

|           3 | 2015   |

|           3 | 2016   |

|           3 | 2017   |

|           3 | 2018   |

|           4 | 1m     ||           4 | 2m     |

+-------------+--------+

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

1

2

3

4

5

+-------------+---------------------+| category_id | name                |

+-------------+---------------------+|           1 | 大,中,小            |

|           2 | 奔驰,宝马           |

|           3 | 2015,2016,2017,2018 ||           4 | 1m,2m               |

+-------------+---------------------+

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

group_concat()函数可以得到表达式结合体的连结值,默认分隔符为逗号,可以通过separator设置为其他分隔符。

注意:group_concat()函数对返回的结果有长度限制,默认为1024字节,不过对于正常的情况已经足够。

关于group_concat()函数的使用可以参考我之前的文章:《mysql函数concat与group_concat使用说明》

执行结果:

1

2

3

4

5

6

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               |

+-------------+---------------------+

2.列转行

创建测试数据表及数据

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

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

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

阅读剩余部分

相关阅读 >>

浅谈mysql join底层原理

mysql存储过程中游标如何遍历

怎么修改mysql root密码?修改数据库root密码的步骤

mysql数据库建表的完整步骤

phpmyadmin无法连接mysql服务器

mysql索引不生效的解决方法

mysql临时表的使用方法详解

mysql怎么取出数据库备份sql

mysqli_query()的用法

如何重置mysql 密码的方法

更多相关阅读请进入《mysql》频道 >>


数据库系统概念 第6版
书籍

数据库系统概念 第6版

机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。



打赏

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码打赏,您说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

分享从这里开始,精彩与您同在

评论

管理员已关闭评论功能...

    正在狠努力加载,请稍候...