對樓上的回答簡化下REPLACE部分：

update user1 b join (

? select user_name,group_concat(

? ? distinct SUBSTRING_INDEX(SUBSTRING_INDEX(mobile,',',t.id),',',-1)

? ? ##直接取第t.id個mobile

? ) mobile from (

? ? select user_name,mobile,length(concat(mobile,','))-length(replace(mobile,',','')) size from user1

? ) a cross join tb_sequence t on a.size>=t.id group by a.user_name

) c on b.user_name = c.user_name set b.mobile = c.mobile;

update user1 b join (select user_name,group_concat(distinct replace(substring(substring_index(mobile,',',b.id),length(substring_index(mobile,',',b.id-1))+1),',','')) mobile from (select user_name,mobile,length(concat(mobile,','))-length(replace(mobile,',','')) size from user1) a join tb_sequence b on a.size>=b.id group by a.user_name) c on b.user_name = c.user_name set b.mobile = c.mobile;只要看了老師講的行轉(zhuǎn)列，這道題就不會太難。其中c表就是老師講行轉(zhuǎn)列的稍加處理之后，在關(guān)聯(lián)更新就好了。

##準(zhǔn)備好要用的表

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 ('孫悟空','斗戰(zhàn)勝佛','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(),(),(),(),(),(),(),(),();

##列轉(zhuǎn)行后的表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表中的重復(fù)記錄

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函數(shù)將mobile轉(zhuǎn)化成以逗號分隔的字符串

create table user1_trans2 as

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

group by user_name,over;

##對原表user1_practice進(jìn)行關(guān)聯(lián)更新

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

set a.mobile = b.mobile;?