如何用sql分割出多条数据？

数据有三条，分别是：

能不能执行查询后拆成 5条数据：

id name

1 {"id":"1","serviceKey": "0001"}

2 {"id":"2","serviceKey": "0002"}

1 {"id":"3","serviceKey": "0003"}

2 {"id":"4","serviceKey": "0004"}

2 {"id":"5","serviceKey": "0005"}

3 {"id":"6","serviceKey": "0006"}

麻烦用纯sql，可以用mysql自带的函数，谢谢

-----下面附上测试测试数据

INSERT INTO aaaa

(name, id)

VALUES('{"id":"1","serviceKey": "0001"}, {"id":"3","serviceKey": "0003"}', '1');

INSERT INTO 63ga.aaaa_2

(name, id)

VALUES('{"id":"2","serviceKey": "0002"}, {"id":"4","serviceKey": "0004"}, {"id":"6","serviceKey": "0006"}', '2');

INSERT INTO 63ga.aaaa_2

(name, id)

VALUES('{"id":"5","serviceKey": "0005"}', '3');

873282620 发布于 2022-10-25 13:52 （编辑于 2022-10-25 14:11）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：7 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共4回答

最佳回答

free_zzLv6中级互助
发布于2022-10-25 15:02（编辑于 2022-10-25 15:03）

SELECT

a.id,trim(REPLACE(substring_index(substring_index( a.rn,',',b.help_topic_id + 1 ),',' ,- 1),"+",'","')) AS rn

FROM

(select id,REPLACE(name,'","',"+") rn from test11) a

JOIN mysql.help_topic b ON b.help_topic_id <

(length(a.rn) - length( replace(a.rn, ',', '') ) + 1)

用户k6280494Lv6资深互助
发布于2022-10-25 13:56

参考 https://blog.csdn.net/weixuan_/article/details/125413298

举报收起评论(1)

873282620(提问者) 该方法不实际，只能拿到前后

2022-10-25 14:11

用户a4836251Lv5初级互助
发布于2022-10-25 14:53

https://blog.csdn.net/shammy_feng/article/details/121267906 拆分之后行转列

蒲公英FZLJLv5初级互助
发布于2022-10-25 14:53（编辑于 2022-10-25 14:59）

参考 https://www.jb51.net/article/248795.htm

SELECT a.id, substring_index(substring_index(a.name, ', ', b.help_topic_id + 1), ', ', - 1) AS name

FROM (select '{"id":"1","serviceKey": "0001"}, {"id":"3","serviceKey": "0003"}' name , '1' id

union all

select '{"id":"2","serviceKey": "0002"}, {"id":"4","serviceKey": "0004"}, {"id":"6","serviceKey": "0006"}', '2'

union all

select '{"id":"5","serviceKey": "0005"}', '3') a

INNER JOIN mysql.help_topic b

ON b.help_topic_id < ((length(a.name) - length(REPLACE(a.name, ', ', '')))/2 + 1)

5关注人数

492浏览人数

最后回答于：2022-10-25 15:03

活动推荐更多

热门课程更多

请选择关闭问题的原因

确定取消

提问

悬赏者（3）

撤回悬赏（3）

添加悬赏

请选择关闭问题的原因