/*
Navicat?MySQL?Data?Transfer

Source?Server?????????:?127.0.0.1_3306
Source?Server?Version?:?50553
Source?Host???????????:?127.0.0.1:3306
Source?Database???????:?test

Target?Server?Type????:?MYSQL
Target?Server?Version?:?50553
File?Encoding?????????:?65001

Date:?2017-07-05?16:37:02
*/

SET?FOREIGN_KEY_CHECKS=0;

--?----------------------------
--?Table?structure?for?tb_sequence
--?----------------------------
DROP?TABLE?IF?EXISTS?`tb_sequence`;
CREATE?TABLE?`tb_sequence`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??PRIMARY?KEY?(`id`)
)?ENGINE=MyISAM?AUTO_INCREMENT=10?DEFAULT?CHARSET=utf8?COMMENT='排序表ID要大于分割的逗號(hào)';

--?----------------------------
--?Records?of?tb_sequence
--?----------------------------
INSERT?INTO?`tb_sequence`?VALUES?('1');
INSERT?INTO?`tb_sequence`?VALUES?('2');
INSERT?INTO?`tb_sequence`?VALUES?('3');
INSERT?INTO?`tb_sequence`?VALUES?('4');
INSERT?INTO?`tb_sequence`?VALUES?('5');
INSERT?INTO?`tb_sequence`?VALUES?('6');
INSERT?INTO?`tb_sequence`?VALUES?('7');
INSERT?INTO?`tb_sequence`?VALUES?('8');
INSERT?INTO?`tb_sequence`?VALUES?('9');

--?----------------------------
--?Table?structure?for?user_kills
--?----------------------------
DROP?TABLE?IF?EXISTS?`user_kills`;
CREATE?TABLE?`user_kills`?(
??`id`?smallint(5)?unsigned?NOT?NULL?AUTO_INCREMENT,
??`user_id`?smallint(5)?unsigned?DEFAULT?NULL,
??`timestr`?timestamp?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP,
??`kills`?smallint(5)?unsigned?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=MyISAM?AUTO_INCREMENT=10?DEFAULT?CHARSET=utf8;

--?----------------------------
--?Records?of?user_kills
--?----------------------------
INSERT?INTO?`user_kills`?VALUES?('1',?'2',?'2013-01-10?00:00:00',?'10');
INSERT?INTO?`user_kills`?VALUES?('2',?'2',?'2013-02-01?00:00:00',?'2');
INSERT?INTO?`user_kills`?VALUES?('3',?'2',?'2013-02-05?00:00:00',?'12');
INSERT?INTO?`user_kills`?VALUES?('4',?'4',?'2013-01-10?00:00:00',?'3');
INSERT?INTO?`user_kills`?VALUES?('5',?'4',?'2013-02-11?00:00:00',?'5');
INSERT?INTO?`user_kills`?VALUES?('6',?'2',?'2013-02-06?00:00:00',?'1');
INSERT?INTO?`user_kills`?VALUES?('7',?'3',?'2013-01-11?00:00:00',?'20');
INSERT?INTO?`user_kills`?VALUES?('8',?'2',?'2013-02-12?00:00:00',?'10');
INSERT?INTO?`user_kills`?VALUES?('9',?'3',?'2013-02-07?00:00:00',?'17');

--?----------------------------
--?Table?structure?for?user1
--?----------------------------
DROP?TABLE?IF?EXISTS?`user1`;
CREATE?TABLE?`user1`?(
??`id`?smallint(5)?unsigned?NOT?NULL?AUTO_INCREMENT,
??`user_name`?varchar(40)?DEFAULT?NULL,
??`over`?varchar(40)?DEFAULT?NULL,
??`mobile`?varchar(255)?NOT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=MyISAM?AUTO_INCREMENT=5?DEFAULT?CHARSET=utf8;

--?----------------------------
--?Records?of?user1
--?----------------------------
INSERT?INTO?`user1`?VALUES?('1',?'唐僧',?'旃檀功德佛',?'123456,456789,774411');
INSERT?INTO?`user1`?VALUES?('2',?'豬八戒',?'凈壇使者',?'987654,3216545,754123');
INSERT?INTO?`user1`?VALUES?('3',?'孫悟空',?'斗戰(zhàn)勝佛',?'123456789,654321,789');
INSERT?INTO?`user1`?VALUES?('4',?'沙僧',?'金身羅漢',?'789422,4456789,654321');

--?----------------------------
--?Table?structure?for?user2
--?----------------------------
DROP?TABLE?IF?EXISTS?`user2`;
CREATE?TABLE?`user2`?(
??`id`?smallint(5)?unsigned?NOT?NULL?AUTO_INCREMENT,
??`user_name`?varchar(40)?DEFAULT?NULL,
??`over`?varchar(40)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=MyISAM?AUTO_INCREMENT=6?DEFAULT?CHARSET=utf8;

--?----------------------------
--?Records?of?user2
--?----------------------------
INSERT?INTO?`user2`?VALUES?('1',?'孫悟空',?'成佛');
INSERT?INTO?`user2`?VALUES?('2',?'牛魔王',?'被降服');
INSERT?INTO?`user2`?VALUES?('3',?'蛟魔王',?'被降服');
INSERT?INTO?`user2`?VALUES?('4',?'鵬魔王',?'被降服');
INSERT?INTO?`user2`?VALUES?('5',?'獅駝王',?'被降服');

SELECT?user_name?,
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?,CONCAT(mobile,',')?as?mobile,
		LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1?size
		FROM?user1?b
)?b??on?a.id?<=?b.size;

sql語(yǔ)句是對(duì)的