MySQL 固定分隔符 列转行

MySQL 固定分隔符 列转行

本文转载于：https://blog.youkuaiyun.com/weixin_39004901/article/details/90257881

基础数据把 name 列以逗号分割的名称都变成一行

select * from tbldsk

id	name
1	洛祺:宇轩
2	鸿铭:林伟:冠杰
3	铭康:嘉鑫:宇鹏:梓域
4	嘉欣:小晴:可恩:玲洁:裕芝
5	啊鬼:阿斗
6	猪仔:小明

本文最下方有基础数据生成

行转列实现过程

1.首先我们算出每个id的name个数：

select
	id,
	name,
	length(name)-length(replace(name, ':', ''))+ 1
from
	tbldsk;

2.我们用substring_index来切分每个id的用户数，第一步骤中得到了每个id的用户数，也就得到了name切分的次数，然后我们利用另一个序列表来构造一个切分视图

select
	a.*,
	length(a.name)-length(replace(a.name, ':', ''))+ 1,
	b.help_topic_id
from
	tbldsk a
left join mysql.help_topic b 
	on b.help_topic_id < (length(a.name)-length(replace(a.name, ':', ''))+ 1);

以上结果的help_topic_id列，即是name的切分位置

3.根据help_topic_id进行切分

3.1根据help_topic_id切分分别得到第一个逗号，第二个逗号…之前的name

select
	a.id,
	SUBSTRING_INDEX(a.name, ':', b.help_topic_id + 1) as name
from
	tbldsk a
left join mysql.help_topic b 
     on b.help_topic_id < (length(a.name)-length(replace(a.name, ':', ''))+ 1);

3.2对上述切分后的tag再处理一次，取最后一个逗号后面的字符串

select
	a.id,
	SUBSTRING_INDEX(SUBSTRING_INDEX(a.name, ':', b.help_topic_id + 1), ':',-1) as name
from
	tbldsk a
left join mysql.help_topic b 
     on
	b.help_topic_id < (length(a.name)-length(replace(a.name, ':', ''))+ 1);

这种列转行的方法有一些前提条件：

1.分割符需要统一： 或者其他英文字符（注意中文字符不行例如 ；、）
2.序列表mysql.help_topic提供的序列help_topic_id需要大于每个id的name数。

基础数据生成

CREATE TABLE `tbldsk` (
  `id` varchar(64) NOT NULL,
  `name` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO test.tbldsk
(id, name)
VALUES('1', '洛祺');
INSERT INTO test.tbldsk
(id, name)
VALUES('1', '宇轩');
INSERT INTO test.tbldsk
(id, name)
VALUES('2', '鸿铭');
INSERT INTO test.tbldsk
(id, name)
VALUES('2', '林伟');
INSERT INTO test.tbldsk
(id, name)
VALUES('2', '冠杰');
INSERT INTO test.tbldsk
(id, name)
VALUES('3', '铭康');
INSERT INTO test.tbldsk
(id, name)
VALUES('3', '嘉鑫');
INSERT INTO test.tbldsk
(id, name)
VALUES('3', '宇鹏');
INSERT INTO test.tbldsk
(id, name)
VALUES('3', '梓域');
INSERT INTO test.tbldsk
(id, name)
VALUES('4', '嘉欣');
INSERT INTO test.tbldsk
(id, name)
VALUES('4', '小晴');
INSERT INTO test.tbldsk
(id, name)
VALUES('4', '可恩');
INSERT INTO test.tbldsk
(id, name)
VALUES('4', '玲洁');
INSERT INTO test.tbldsk
(id, name)
VALUES('4', '裕芝');
INSERT INTO test.tbldsk
(id, name)
VALUES('5', '啊鬼');
INSERT INTO test.tbldsk
(id, name)
VALUES('5', '阿斗');
INSERT INTO test.tbldsk
(id, name)
VALUES('6', '猪仔');
INSERT INTO test.tbldsk
(id, name)
VALUES('6', '小明');

本文转载于：https://blog.youkuaiyun.com/weixin_39004901/article/details/90257881