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、插入数据

二、列转行

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, ',', ''))

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

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