原文: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)