mysql 将行拆分为多列和多行

Question

我有一个具有以下结构的表，这些值在列 assoc 中。

| id | assoc |

| 1 | |3|-1|107|-4|146|-6| |

| 2 | |19|-3|107|-5| |

| 3 | |42|-1| |

可以看到here

这是一个错误的mysql表结构。所以我认为正确的结构必须是:

| id | assoc | attrib | order |

| 1  | 3 | 1 | 1 |

| 1  | 107 | 4 | 2 |

| 1  | 146 | 6 | 3 |

| 2  | 19 | 3 | 1 |

| 2  | 107 | 5 | 2 |

| 3  | 42 | 1 | 1 |

可以在 phpmyadmin 上使用 mysql 脚本吗？

Answer 1

SET @prev := null;

SET @cnt := 0;

SELECT id,blah,mah,IF(@prev <> id, @cnt := 1, @cnt := @cnt + 1) AS rank, @prev := id 
FROM(
SELECT id,REPLACE(SUBSTRING_INDEX(assoc,'|',2),'|','')*1 as blah, 
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'-0'),'|',3),'-',-1)as mah FROM table1
UNION ALL
SELECT id,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'|0'),'|',4),'|',-1)*1 as blah,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'-0'),'|',5),'-',-1)as mah FROM table1
UNION ALL
SELECT id,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'|0'),'|',6),'|',-1)*1 as blah,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'-0'),'|',7),'-',-1)as mah FROM table1
)x
WHERE   x.mah !='0'
ORDER BY x.id ,x.blah 

FIDDLE