MySQL列转行
有些列的值是逗号分隔的：“1,2,3”，偶然候需要把这样的一列拆分成多行，本文介绍拆分方法。一、数据预备
1.建表
（1）工单详情表：
CREATE TABLE `workorder` (
`id` varchar(255) CHARACTER SET utf8mb4 NOT NULL COMMENT '工单id',
`processors` varchar(255) DEFAULT NULL COMMENT '处理人，英文逗号","分隔',
`desc` varchar(255) DEFAULT NULL COMMENT '工单描述',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单详情表'; （2）员工信息表：
CREATE TABLE `staff` (
`id` varchar(255) NOT NULL COMMENT '员工id',
`name` varchar(255) DEFAULT NULL COMMENT '员工姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表'; 2、插入数据
https://dis.qidao123.com/imgproxy/aHR0cHM6Ly9pLWJsb2cuY3NkbmltZy5jbi9kaXJlY3QvMGNmMWZjYWVjY2ZmNGRiMWJkNjMzMjk2Zjg0ZGQ5YmYucG5n
https://dis.qidao123.com/imgproxy/aHR0cHM6Ly9pLWJsb2cuY3NkbmltZy5jbi9kaXJlY3QvZTRhMGRiM2YzN2FmNDM2YWFkMDU5ZGI1ZGFkNDQwMTUucG5n
二、列转行
1.把工单的“处理人”拆分成多行
select w.id workorderId,
w.desc workorderDesc,
substring_index(SUBSTRING_INDEX(w.processors, ',', hp.help_topic_id + 1), ',', -1) processorId
from workorder w
left join mysql.help_topic hp on hp.help_topic_id <= length(w.processors) - length(REPLACE(w.processors, ',', '')) https://dis.qidao123.com/imgproxy/aHR0cHM6Ly9pLWJsb2cuY3NkbmltZy5jbi9kaXJlY3QvZGUzYmUxM2YyMjkzNDlmNzg2MjA5NTFlOWJjNTdhMmMucG5n
2.添加姓名列
select a.*, s.name
from (
select w.id workorderId,
w.desc workorderDesc,
substring_index(SUBSTRING_INDEX(w.processors, ',', hp.help_topic_id + 1), ',', -1) processorId
from workorder w
left join mysql.help_topic hp on hp.help_topic_id <= length(w.processors) - length(REPLACE(w.processors, ',', ''))
) a
left join staff s on s.id = a.processorId https://dis.qidao123.com/imgproxy/aHR0cHM6Ly9pLWJsb2cuY3NkbmltZy5jbi9kaXJlY3QvMDQzZWIwMzM0YzQxNDgyZWJkYWUyN2U4OGI3OGEyNWIucG5n
3.把数字拼接的列转换成笔墨拼接的列
把工单workorder表的“processors”列的员工id转成员工姓名，用逗号拼接
select a.workorderId,
a.workorderDesc,
GROUP_CONCAT(s.name) processors
from (
select w.id workorderId,
w.desc workorderDesc,
substring_index(SUBSTRING_INDEX(w.processors, ',', hp.help_topic_id + 1), ',', -1) processorId
from workorder w
left join mysql.help_topic hp on hp.help_topic_id <= length(w.processors) - length(REPLACE(w.processors, ',', ''))
) a
left join staff s on s.id = a.processorId
group by a.workorderId https://dis.qidao123.com/imgproxy/aHR0cHM6Ly9pLWJsb2cuY3NkbmltZy5jbi9kaXJlY3QvYTg5ODRmZGU0OWI4NGM2YWE3YTlhNTVkYzgzNDVjZmQucG5n
页:
[1]