mysql行转列(拆分单个字段字符串值场景)
根据某个字段值,如根据“,”分割,行转成多列
可以借助一个序号表,该表中除了连续的ids没有其它字段,ids的值范围取决于"一"中存储的信息拆分后的数量(可以是大于等于这个数量)。
SELECT
ids,
id,
APPLY_ID,
REPLACE(
SUBSTRING_INDEX(REASON, ',', a.ids),
CONCAT(
SUBSTRING_INDEX(REASON, ',', a.ids - 1),
','
),
''
)AS REASON
FROM
t_squence a
CROSS JOIN(
SELECT id,APPLY_ID,
CONCAT(REASON, ',')AS REASON,
LENGTH(REASON)- LENGTH(REPLACE(REASON, ',', ''))+ 1 AS size
FROM(
SELECT id,APPLY_ID,left(substring(REPLACE(REASON,',,',','),2),length(substring(REPLACE(REASON,',,',','),2))-1) REASON from (select id,APPLY_ID,CERT_NO,
REPLACE(GROUP_CONCAT(FAIL_REASON),'|',',') AS REASON
from rcs_finance_ser_result rfsr
where rfsr.final_result='AD' and rfsr.cert_no='411201199110164491' GROUP BY rfsr.CERT_NO )t
)tt
)b ON a.ids <= b.size