qidao123.com技术社区-IT企服评测·应用市场

标题: MySQL列转行 [打印本页]

---

作者: 铁佛    时间: 2024-11-14 05:25
标题: MySQL列转行
有些列的值是逗号分隔的：“1,2,3”，偶然候需要把这样的一列拆分成多行，本文介绍拆分方法。
一、数据预备

1.建表

（1）工单详情表：

1. CREATE TABLE `workorder` (
2.   `id` varchar(255) CHARACTER SET utf8mb4 NOT NULL COMMENT '工单id',
3.   `processors` varchar(255) DEFAULT NULL COMMENT '处理人，英文逗号","分隔',
4.   `desc` varchar(255) DEFAULT NULL COMMENT '工单描述',
5.   PRIMARY KEY (`id`) USING BTREE
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单详情表';

复制代码

（2）员工信息表：

1. CREATE TABLE `staff` (
2.   `id` varchar(255) NOT NULL COMMENT '员工id',
3.   `name` varchar(255) DEFAULT NULL COMMENT '员工姓名',
4.   PRIMARY KEY (`id`)
5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表';

复制代码

2、插入数据

二、列转行

1.把工单的“处理人”拆分成多行

1. select w.id workorderId,
2.     w.desc workorderDesc,
3.           substring_index(SUBSTRING_INDEX(w.processors, ',', hp.help_topic_id + 1), ',', -1) processorId
4. from workorder w
5. left join mysql.help_topic hp on hp.help_topic_id <= length(w.processors) - length(REPLACE(w.processors, ',', ''))

复制代码

2.添加姓名列

1. select a.*, s.name
2. from (
3.                 select w.id workorderId,
4.                                 w.desc workorderDesc,
5.                                 substring_index(SUBSTRING_INDEX(w.processors, ',', hp.help_topic_id + 1), ',', -1) processorId
6.                 from workorder w
7.                 left join mysql.help_topic hp on hp.help_topic_id <= length(w.processors) - length(REPLACE(w.processors, ',', ''))
8. ) a
9. left join staff s on s.id = a.processorId

复制代码

3.把数字拼接的列转换成笔墨拼接的列

把工单workorder表的“processors”列的员工id转成员工姓名，用逗号拼接

1. select a.workorderId,
2.                 a.workorderDesc,
3.                 GROUP_CONCAT(s.name) processors
4. from (
5.                 select w.id workorderId,
6.                                 w.desc workorderDesc,
7.                                 substring_index(SUBSTRING_INDEX(w.processors, ',', hp.help_topic_id + 1), ',', -1) processorId
8.                 from workorder w
9.                 left join mysql.help_topic hp on hp.help_topic_id <= length(w.processors) - length(REPLACE(w.processors, ',', ''))
10. ) a
11. left join staff s on s.id = a.processorId
12. group by a.workorderId

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 qidao123.com技术社区-IT企服评测·应用市场 (https://dis.qidao123.com/)

Powered by Discuz! X3.4