MySQL字段内容拆分及合并

导语：MySQL字段内容拆分及合并

1. 创建测试表及数据

-- 创建一张tb_stu表CREATE TABLE tb_user(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10) COMMENT &39;,c_no VARCHAR(64) COMMENT &39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);INSERT INTO tb_user(NAME,c_no) VALUES(&39;,&39;);-- 创建一张剑名create table tb_sword(id int primary key AUTO_INCREMENT,c_name varchar(4)) comment &39;;insert into tb_sword(c_name)values(&39;);insert into tb_sword(c_name)values(&39;);insert into tb_sword(c_name)values(&39;);insert into tb_sword(c_name)values(&39;);insert into tb_sword(c_name)values(&39;);insert into tb_sword(c_name)values(&39;);insert into tb_sword(c_name)values(&39;);insert into tb_sword(c_name)values(&39;);insert into tb_sword(c_name)values(&39;);

两张表内容如下：

tb_user

tb_sword

2. 数据拆分及合并

需求： 使用一条SQL获得tb_user表中每个人持有的剑名（剑名用“|”分隔），即得到如下结果

拆解需求：

1） 先将tb_user表中的c_no按逗号拆分

2）将拆分后c_no中的各个id与tb_sword中的id关联，获取剑名

3) 最后将每一个user对应的剑名合并成一个字段

分段SQL如下：

步骤1：

每一个user的c_no按逗号拆分为对应的c_id，此方法需借助于mysql.help_topic表

SELECT a.id,a.name,a.c_no, SUBSTRING_INDEX( SUBSTRING_INDEX( a.c_no, &39;, b.help_topic_id + 1 ), &39;,- 1 ) c_idFROM tb_user a JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.c_no ) - LENGTH( REPLACE ( a.c_no, &39;, &39; ) ) + 1 )ORDER BY a.id

结果如下：

步骤2：关联获取每个id对应的剑名

SELECT a2.id,a2.name,a2.c_no,a2.c_id,b2.c_nameFROM (SELECT a.id,a.name,a.c_no, SUBSTRING_INDEX( SUBSTRING_INDEX( a.c_no, &39;, b.help_topic_id + 1 ), &39;,- 1 ) c_idFROM tb_user a JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.c_no ) - LENGTH( REPLACE ( a.c_no, &39;, &39; ) ) + 1 )ORDER BY a.id) a2, -- a2表即步骤1中拆分的结果tb_sword b2 WHERE a2.c_id =b2.id -- 关联，相当于inner join（或者join）

结果如下

步骤3：

将每个人的剑名合并为1个字段显示，并用&34; 符合合并

SELECT a2.id,a2.name,a2.c_no,GROUP_CONCAT(b2.c_name SEPARATOR &39; ) sword_name-- SEPARATOR 指定分隔富，不加默认为逗号分隔FROM (SELECT a.id,a.name,a.c_no,SUBSTRING_INDEX( SUBSTRING_INDEX( a.c_no, &39;, b.help_topic_id + 1 ), &39;,- 1 ) c_idFROM tb_user aJOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.c_no ) - LENGTH( REPLACE ( a.c_no, &39;, &39; ) ) + 1 )ORDER BY a.id) a2,tb_sword b2WHERE a2.c_id =b2.idGROUP BY a2.id

结果如下：

实现需求