mysql如何使用SUBSTRING_INDEX函数对对应的字符串实现列转行的效果？-灵析社区

用MySQL的SUBSTRING_INDEX和SUBSTRING函数来拆分字符串 SET SESSION group_concat_max_len = 1000000; DROP TEMPORARY TABLE IF EXISTS temp_data; CREATE TEMPORARY TABLE temp_data AS SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(IMEI_rel, '使用过', -1), '】', numbers1.n), '【', -1) AS serial_number, SUBSTRING_INDEX(SUBSTRING_INDEX(IMEI_rel, '】', numbers1.n), '【', -1) AS data_segment FROM phone_number_demo JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS numbers1 ON CHAR_LENGTH(IMEI_rel) - CHAR_LENGTH(REPLACE(IMEI_rel, '【', '')) >= numbers1.n - 1 ORDER BY IMEI_rel, numbers1.n; DROP TEMPORARY TABLE IF EXISTS temp_result; CREATE TEMPORARY TABLE temp_result AS SELECT serial_number, SUBSTRING_INDEX(SUBSTRING_INDEX(data_segment, ',', 1), '[', -1) AS date, SUBSTRING_INDEX(SUBSTRING_INDEX(data_segment, ',', 2), ',', -1) AS region, SUBSTRING_INDEX(SUBSTRING_INDEX(data_segment, ',', 3), ',', -1) AS city, SUBSTRING_INDEX(SUBSTRING_INDEX(data_segment, ',', -1), ']', 1) AS phone FROM temp_data JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS numbers2 ON CHAR_LENGTH(data_segment) - CHAR_LENGTH(REPLACE(data_segment, '[', '')) >= numbers2.n - 1 ORDER BY serial_number, numbers2.n; SELECT SUBSTRING(serial_number, 2, CHAR_LENGTH(serial_number) - 2) AS serial_number, date, region, city, phone FROM temp_result;