##准备好要用的表

create table user1_practice(id int not null auto_increment primary key,

user_name varchar(3),

over varchar(5),

mobile varchar(100));

insert into user1_practice(user_name,over,mobile) values ('唐僧','旃檀功德佛','12112345678,14112345678,12112345678');

insert into user1_practice(user_name,over,mobile) values ('猪八戒','净坛使者','12144643321,14144643321');

insert into user1_practice(user_name,over,mobile) values ('孙悟空','斗战胜佛','12166666666,14166666666,18166666666,18166666666');

insert into user1_practice(user_name,over,mobile) values ('沙僧','金身罗汉','12198343214,14198343214');

##建一个序列表

create tb_sequence(id int not null auto_increment primary key);

insert into tb_sequence values(),(),(),(),(),(),(),(),();

##列转行后的表user1_trans1

create table user1_trans1 as 

select a.id,user_name,over,

replace(substring(substring_index(mobile,',',a.id),char_length(substring_index(mobile,',',a.id-1))+1),',','') as mobile 

from tb_sequence a cross join(

select user_name,over,

concat(mobile,',') as mobile,

length(mobile)-length(replace(mobile,',',''))+1 as size

from user1_practice b) b on a.id <= b.size;

##删除user1_trans1表中的重复记录

delete a from user1_trans1 a join (

select user_name,over,mobile,count(*),max(id) as id 

from user1_trans1  group by user_name,over,mobile having count(*) > 1  ) b

on a.user_name = b.user_name 

and a.over = b.over

and a.mobile = b.mobile

where a.id < b.id;

##用group_concat函数将mobile转化成以逗号分隔的字符串

create table user1_trans2 as

select user_name,over,group_concat(mobile) as mobile from user1_trans1

group by user_name,over;

##对原表user1_practice进行关联更新

update user1_practice a inner join user1_trans2 b on a.user_name = b.user_name

set a.mobile = b.mobile; 

这个要看应用的场竟来定了。