MySQL使用列转行(指定字符串分割)
问题描述
原始数据:AAA,BBB,CCC,DDD,EEE,FFF
期望数据:
value |
---|
AAA |
BBB |
CCC |
DDD |
EEE |
FFF |
实现SQL:
SELECT
查询表的自增id,
substring_index(
substring_index(
需要分割的列,
',',
b.help_topic_id + 1
),
',' ,- 1
) AS 别名,其他字段名
FROM
(SELECT @rownum := @rownum+1 AS id,其他字段名, 需要分割的列
FROM (SELECT @rownum:=0)r , 表名 as t
) a
JOIN mysql.help_topic b ON b.help_topic_id < (
length(需要分割的列) - length(
REPLACE (需要分割的列, ',', '')
) + 1
)
示例SQL:
SELECT
id,
substring_index(
substring_index(
(CHILD_ORG),
',',
b.help_topic_id + 1
),
',' ,- 1
) AS tt,ORG_NO
FROM
(SELECT @rownum := @rownum+1 AS id,ORG_NO, CHILD_ORG
FROM (SELECT @rownum:=0)r , sc_child_org as t
) a
JOIN mysql.help_topic b ON b.help_topic_id < (
length(CHILD_ORG) - length(
REPLACE (CHILD_ORG, ',', '')
) + 1
)