福彩网网站建设方案,网站备案取名,山西建设执业注册管理中心网站,环球网最新新闻目录 效果如下源数据变更后的数据 方法第一种示例SQL和业务结合在一起使用 第二种示例SQL和业务结合在一起使用 结论 效果如下
源数据 变更后的数据 方法
第一种
先执行下面的SQL#xff0c;看不看能不能执行#xff0c;如果有结果#xff0c;代表数据库版本是可以的看不看能不能执行如果有结果代表数据库版本是可以的可以看下面和自己表关联的SQL如果不行用第二种。
示例SQL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(7654,7698,7782,7788,,,help_topic_id1),,,-1) AS num
FROM mysql.help_topic
WHERE help_topic_id LENGTH(7654,7698,7782,7788)-LENGTH(REPLACE(7654,7698,7782,7788,,,))1和业务结合在一起使用
SELECTa.store_signer_name,substring_index( substring_index( a.concatStoreId, ,, b.help_topic_id 1 ), ,, - 1 ) AS concatStoreId FROM(select store_signer_nameconcatStoreId from test) aINNER JOIN mysql.help_topic b ON b.help_topic_id (length( a.concatStoreId ) - length(REPLACE ( a.concatStoreId, ,, )) 1)其核心在于mysql.help_topic但是版本太低的数据库版本不支持如果不支持可以用下面第二种。
第二种
示例SQL
SELECTSUBSTRING_INDEX(SUBSTRING_INDEX(table_name.csv_values, ,, numbers.n), ,, -1) AS split_value
FROMtable_nameINNER JOIN(SELECT 1 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 UNION ALLSELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALLSELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALLSELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALLSELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40) numbersON CHAR_LENGTH(table_name.csv_values) - CHAR_LENGTH(REPLACE(table_name.csv_values, ,, )) numbers.n - 1;在上面的查询中因为我逗号分隔的最大个数是36所以我添加了40个UNION ALL SELECT子句以生成数字序列1到40。你可以根据需要调整这个序列的长度。
请注意如果你的逗号分隔值个数大于40那么你需要相应地增加数字序列的长度。
和业务结合在一起使用
SELECT
table_name.store_signer_name,
table_name.store_signer_contact,SUBSTRING_INDEX(SUBSTRING_INDEX(table_name.concatStoreId, ,, numbers.n), ,, -1) AS store_id
FROM(select store_signer_name,store_signer_contact,GROUP_CONCAT(store_id) concatStoreId from t_store_esgin_info where business_status 1003 and type 0 and start_year 2023 group by store_signer_name,store_signer_contact having count(1) 1) table_nameINNER JOIN(SELECT 1 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 UNION ALLSELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALLSELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALLSELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALLSELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40) numbersON CHAR_LENGTH(table_name.concatStoreId) - CHAR_LENGTH(REPLACE(table_name.concatStoreId, ,, )) numbers.n - 1;
结论
如果Mysql版本较低使用第二种如果可以执行第一种示例SQL那么推荐使用第一种动态的。
