mysql表数据行列转换方法
来源:互联网 发布:mac菜单栏如何添加工具 编辑:程序博客网 时间:2024/05/16 10:15
开发过程中,因为历史原因或性能原因,需要对表的列数据转为行数据,或行数据转换为列数据使用,本文将介绍mysql表数据行列转换的方法,提供完整演示例子及sql技巧。
1.行转列
创建测试数据表及数据
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 |+-------------+--------+
行转列后,期望得到以下结果
+-------------+---------------------+| 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使用说明》
执行结果:
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 |+-------------+---------------------+
列转行后,期望得到以下结果
+-------------+--------+| 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,',',b.category_id),',',-1) as name from `option2` as ajoin `option2` as b on b.category_id<=(length(a.name) - length(replace(a.name,',',''))+1)order by a.category_id,b.category_id;
执行结果:
mysql> select a.category_id,substring_index(substring_index(a.name,',',b.category_id),',',-1) as name from `option2` as a -> join `option2` as b on b.category_id<=(length(a.name) - length(replace(a.name,',',''))+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 |+-------------+--------+
阅读全文
1 0
- mysql表数据行列转换方法
- MySql表数据行列转换
- mysql行列转换方法总结
- mysql行列转换方法总结
- mysql行列转换方法总结
- mysql行列转换方法总结
- mysql行列转换方法总结
- mysql行列转换方法总结
- mysql行列转换方法总结
- mysql行列转换方法总结
- mysql行列转换方法总结
- 毫无规律的表数据的行列转换方法!!
- mySql 数据库表行列转换
- MySQL 列变行 数据变换 行列转换
- mysql行列转换方法总结叭
- mysql交叉表,行列动态转换
- mysql交叉表,行列动态转换
- Mysql行列转换
- 最近公共祖先
- CodeForces
- select 函数的使用方法
- Java入门笔记-(语言基础)
- 二分图匹配详解
- mysql表数据行列转换方法
- 小白开始
- 利用重写url机制简单实现验证码换一张的功能
- Java日期时间
- JVM垃圾回收
- 启动保护模式的程序
- 【LeetCode】String-to-int
- MaterialDesign-Toolbar(五)
- [职业生涯之关于创业与打工]由小摊生意联想到的