mysql根据逗号将一行数据拆分成多行数据
1、原始数据演示
2、处理结果演示
3、sql语句
SELECT
a.id,
a. NAME,
substring_index(
substring_index(
a.shareholder,
',',
b.help_topic_id + 1
),
',' ,- 1
) AS shareholder
FROM
company a
JOIN mysql.help_topic b ON b.help_topic_id < (
length(a.shareholder) - length(
REPLACE (a.shareholder, ',', '')
) + 1
)
4、附录 基本表建立语句
CREATE TABLE `company` (
`id` int(20) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`shareholder` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `company` VALUES ('1', '阿里巴巴', '马云');
INSERT INTO `company` VALUES ('2', '淘宝', '马云,孙正义');
