MySQL 字段值按照分隔符拆分成列（行转列）

迈不过友情╰ 2024-04-08 09:48 23阅读 0赞

拆分前：

![3cd1a5bb3d8742d891b15d585513dfb3.png][]

拆分后：

![8beff249096c4445a5d730d9a9d01b34.png][]

SQL：

WITH RECURSIVE planting_situation AS (
    	SELECT 
    		dist_code, dist_name,
    		substr(concat(planting_varieties, '、'), 1, instr(concat(planting_varieties, '、'), '、') - 1) planting_varieties,
    		substr(concat(planting_varieties, '、'), instr(concat(planting_varieties, '、'), '、') + 1) str
    	FROM t_big_mushroom_planting_situation tbmps 
    	UNION ALL
    	SELECT 
    		dist_code, dist_name,
    		substr(str, 1, instr(str, '、') - 1) planting_varieties,
    		substr(str, instr(str, '、') + 1) str
    	FROM planting_situation
    	WHERE instr(str, '、') > 0
    )
    SELECT * FROM planting_situation
    ORDER BY dist_code

[3cd1a5bb3d8742d891b15d585513dfb3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/c3d2056b1c8541fab34440a375fd282f.png
[8beff249096c4445a5d730d9a9d01b34.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/4bf028ba4853434092491a481d9194f8.png

MySQL 字段值按照分隔符拆分成列（行转列）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读