个回答

• Edwardsr

  2019-01-06 00:23:14

  对楼上的回答简化下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;

  
  

  1

• 王飞fly

  2018-08-02 21:44:02

  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;只要看了老师讲的行转列，这道题就不会太难。其中c表就是老师讲行转列的稍加处理之后，在关联更新就好了。

  1

• Lindsay_Hu

  2016-08-23 09:52:34

  ##准备好要用的表

  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; 

  
  

  6