mysql 逗号与分隔符_使用逗号作为分隔符在MySQL中解析

我将测试数据插入SQLFiddle中的测试模式,然后运行下面的查询.

这是查询：

SELECT

COALESCE(NORMALIZED_TABLE1.NAME, TABLE2.NAME) AS NAME,

COALESCE(NORMALIZED_TABLE1.USERID, TABLE2.USERID) AS USERID

FROM (

SELECT NAME,

SUBSTRING(

USERID

FROM CASE

WHEN INDEX_TABLE.POS = 1 THEN 1

ELSE INDEX_TABLE.POS + 1

END

FOR CASE LOCATE(',', USERID, INDEX_TABLE.POS + 1)

WHEN 0 THEN CHARACTER_LENGTH(USERID) + 1

ELSE LOCATE(',', USERID, INDEX_TABLE.POS + 1)

END

- CASE

WHEN INDEX_TABLE.POS = 1 THEN 1

ELSE INDEX_TABLE.POS + 1

END

) AS USERID

FROM TABLE1

INNER JOIN (

SELECT @rownum:=@rownum+1 POS

FROM (

SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

) a, (

SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

) b, (

SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

) c, (SELECT @rownum:=0) r

) INDEX_TABLE

ON INDEX_TABLE.POS <= CHAR_LENGTH(TABLE1.USERID)

AND (

INDEX_TABLE.POS = 1

OR SUBSTRING(USERID FROM INDEX_TABLE.POS FOR 1) = ','

)

) AS NORMALIZED_TABLE1

LEFT OUTER JOIN TABLE2

ON NORMALIZED_TABLE1.NAME = TABLE2.NAME

AND NORMALIZED_TABLE1.USERID = TABLE2.USERID

WHERE TABLE2.NAME IS NULL;