mysql生成序列表，生成100个数字，用于拆分字段，一行拆多行

joewdc

已于 2023-11-13 16:44:08 修改

阅读量1.1k

点赞数

分类专栏：笔记文章标签： mysql 数据库

于 2022-07-05 11:48:33 首次发布

本文链接：https://blog.csdn.net/weixin_48277834/article/details/125616782

版权

笔记专栏收录该内容

24 篇文章 0 订阅

订阅专栏

--生产序列表
	create table sequence_table 
						(id int 
						);
						insert into sequence_table
						select @xi:=@xi+1 as xc from 
						(select 1 union select 2 union select 3 union select 4 union select 5) xc1,
						(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 union select 10 union select 11 union select 12
						union select 13
						union select 14 union select 15 union select 16 union select 17
						union select 18 union select 19 union select 20 
						
						
						
						) xc2,
						(select @xi:=-1) xc0;

--一行拆分行代码
SELECT 
SUBSTRING_INDEX(SUBSTRING_INDEX(st.product_patent_country,',',a.id+1),',', -1) AS product_patent_country,
esku ,st.saas_id
FROM saas_erp_product_center.tb_product st
JOIN sequence_table a ON a.id <
(LENGTH(st.product_patent_country)-LENGTH(REPLACE(st.product_patent_country,',',''))+1)
where st.is_newest = 1
and st.product_patent_country <>'否'
AND st.delete_time IS NULL 
group by 1,2,3