行转列，根据未知逗号分割——Mysql版

SELECT PK_ID, SJKZRZJLX, SJKZRZJDM FROM BFD.bfd_ftykhx WHERE DATA_DT 2026-01-31 AND PK_ID IN ( -- 第一步计算每条记录的拆分数量 SELECT T1.PK_ID/*, t1.SJKZRZJDM AS 原始字符串_JDM, t1.SJKZRZJLX AS 原始字符串_JLX, T1.逗号数量, T1.SPLIT_COUNT AS 应拆分行数, T2.N AS 拆分序号, SUBSTRING_INDEX(SUBSTRING_INDEX(T1.SJKZRZJDM, ,, T2.N), ,, -1) AS 拆分后的值DM, SUBSTRING_INDEX(SUBSTRING_INDEX(T1.SJKZRZJLX, ,, T2.N), ,, -1) AS 拆分后的值LX*/ FROM (SELECT T1.PK_ID, t1.SJKZRZJDM, t1.SJKZRZJLX, -- 计算逗号数量 LENGTH(SJKZRZJLX) - LENGTH(REPLACE(SJKZRZJLX, ,, )) AS 逗号数量, LENGTH(T1.SJKZRZJLX) - LENGTH(REPLACE(T1.SJKZRZJLX, ,, )) 1 AS SPLIT_COUNT -- 计算当前记录要拆分的项数逗号1 FROM BFD.bfd_ftykhx T1 WHERE DATA_DT 2026-01-31 AND (SJKZRZJLX LIKE %B01% OR SJKZRZJLX LIKE %B08%)) T1 -- 第二步仅为当前记录生成需要的数字 INNER JOIN (SELECT row : row 1 as n from (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) a, (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) b, (SELECT row : 0) r) T2 ON T2.N T1.SPLIT_COUNT ORDER BY T1.PK_ID, T2.n);