mysql - SQL中如何将一个拆分为多个

Question

你好，我在用 sql 拆分它时遇到了问题

我使用 stackoverflow_sample_universal 数据库

我的 table 现在看起来像这样

id          tags        
13649012    vb.net::vb.net-2010     
19329707    c++::c::boost::makefile::cmake      
17394734    c#::html::asp.net-mvc-4     
26583319    java::class::casting::classloader       
18021406    ios::objective-c::constraints::autolayout       
20670104    visual-studio-2010::sharepoint::.net-framework-version::multi-

我要实现的是这个

id          tags
13649012    vb.net
13649012    vb.net-2010
19329707    c++
19329707    c
19329707    boost
19329707    makefile
19329707    cmake
17394734    c#
17394734    html
17394734    asp.net-mvc-4

Answer 1

SELECT id, SUBSTRING_INDEX(tags, '::', 1) AS tags FROM table1 
UNION
SELECT  id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, '::', 2),'::',-1) AS tags FROM table1
UNION
SELECT  id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, '::', 3),'::',-1) AS tags FROM table1
UNION
SELECT  id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, '::', 4),'::',-1) AS tags FROM table1
UNION
SELECT  id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, '::', 5),'::',-1) AS tags FROM table1
UNION
SELECT  id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, '::', 6),'::',-1) AS tags FROM table1
ORDER BY id