MySQL 一列数据拆分并查询对应数据后合并为一行

功能：MySQL 将一列数据进行根据分隔符进行拆分后查询对应数据并合并为一行。

效果：将答题表text列根据逗号分割为每个选项，根据选项到答题选项表查询对应选项数据option_text后合并为一行拼接（格式 选项+. +选项内容）。

一、数据处理

1. 答题表数据（存放答题信息）

2. 答题选项表数据（存放答题选项信息）

3. 效果图

3.1 实现代码

SELECT 
	a.answer_id, a.text,
	GROUP_CONCAT(
		CONCAT(
			SUBSTRING_INDEX(SUBSTRING_INDEX(a.text, ',', b.help_topic_id + 1), ',' ,- 1), # 得到当前选项
			'. ',
			(
				SELECT answer_option.option_text 
				FROM answer_option 
				WHERE answer_option.option_key = 
					SUBSTRING_INDEX(SUBSTRING_INDEX(a.text, ',', b.help_topic_id + 1), ',' ,- 1) # 得到当前选项
			) # 查询当前选项对应的值
		) SEPARATOR '     ' # 间隔符
	) AS result
FROM answer a
JOIN mysql.help_topic b ON b.help_topic_id < (LENGTH(a.text) - LENGTH(REPLACE (a.text, ',', '')) + 1)
GROUP BY answer_id

3.2 使用函数介绍

# 函数介绍
# SUBSTRING_INDEX 截取
# 	例：SUBSTRING_INDEX('a,b,c,d',',',2) 结果 a,b
#	例：SUBSTRING_INDEX('a,b,c',',',-1) 结果 c  注意：负数从后开始算
# CONCAT 拼接字符串
# 	例：CONCAT('A','，', '选项a') 结果 A. 选项a
# 	例：CONCAT('C','，', '选项c') 结果 C. 选项c
# GROUP_CONCAT 将group by产生的同一个分组中的值连接起来，返回一个字符串结果
# SEPARATOR 配合 GROUP_CONCAT 使用 合并成一行且在两值中间拼接字符
# mysql.help_topic 一个系统表，这里用来当索引使用
# LENGTH 计算长度
# 	例：LENGTH('a,b,c') 结果 5
# 	例：LENGTH('abc') 结果 3
# GROUP BY 分组
#	例：GROUP BY answer_id 根据answer_id进行分组

 

二、建表语句

1. 答题表.sql

DROP TABLE IF EXISTS `answer`;
CREATE TABLE `answer` (
  `answer_id` int(255) NOT NULL AUTO_INCREMENT COMMENT '答题id',
  `text` varchar(255) DEFAULT NULL COMMENT '答题内容',
  PRIMARY KEY (`answer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='答题表';
INSERT INTO `answer` VALUES ('1', 'A,D');
INSERT INTO `answer` VALUES ('2', 'D,C');
INSERT INTO `answer` VALUES ('3', 'B,C,A');

2. 答案选项表.sql

DROP TABLE IF EXISTS `answer_option`;
CREATE TABLE `answer_option` (
  `option_id` int(255) NOT NULL AUTO_INCREMENT COMMENT '选项id',
  `option_key` varchar(255) DEFAULT NULL COMMENT '选项',
  `option_text` varchar(255) DEFAULT NULL COMMENT '内容',
  PRIMARY KEY (`option_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `answer_option` VALUES ('1', 'A', '选项a');
INSERT INTO `answer_option` VALUES ('2', 'B', '选项b');
INSERT INTO `answer_option` VALUES ('3', 'E', '选项e');
INSERT INTO `answer_option` VALUES ('4', 'D', '选项d');
INSERT INTO `answer_option` VALUES ('5', 'C', '选项c');
INSERT INTO `answer_option` VALUES ('6', 'F', '选项f');