MySQL -- 一行拆成多行 split

原文：https://www.cnblogs.com/gered/p/10797012.html

 

 

 提取SUCCESS_NODE不在ALL_NODE中的值单独成一行，效果如下：

SELECT *  FROM (
    SELECT 
        B.ID AS ID,
        B.TASK_NAME,
        SUBSTRING_INDEX(SUBSTRING_INDEX(B.ALL_NODE, ',', A.HELP_TOPIC_ID + 1),',',-1) AS NODE
    FROM MYSQL.HELP_TOPIC A, 
            T_TEST B
    WHERE A.HELP_TOPIC_ID < LENGTH(B.ALL_NODE) - LENGTH(REPLACE(B.ALL_NODE, ',', '')) + 1
) A
WHERE NOT EXISTS (SELECT 1 FROM 
(
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(B.SUCCESS_NODE, ',', A.HELP_TOPIC_ID + 1),',',-1) AS NODE,
        B.ID AS ID
    FROM MYSQL.HELP_TOPIC A, 
            T_TEST B
    WHERE A.HELP_TOPIC_ID < LENGTH(B.SUCCESS_NODE) - LENGTH(REPLACE(B.SUCCESS_NODE, ',', '')) + 1
) S WHERE S.ID = A.ID AND S.NODE = A.NODE)