php - mysql-将列动态拆分为行

我有表training，我想将training_name列值拆分为多行：

SLNO Category Training_name
1     A        1,5,9,15,12,16
2     B        2,6,10,17
3     C        1,3,7,19,14,18

我使用了下面的查询，但是使用这个查询，我只能分成两行。。。

SELECT training.SLNO,training.CATEGORY, SubString_Index(training.TRAINING_NAME, ',', 1) AS TRAINING_NAME FROM training UNION ALL SELECT training.SLNO,training.CATEGORY, SubString_Index(training.TRAINING_NAME, ',', -1) FROM training

我正试着把下面的桌子拿过来，请帮帮我

SLNO Category Training_name
1      A        1
1      A        5
1      A        9
1      A        15
1      A        12
1      A        16
2      B        2
2      B        6
2      B        10
2      B        17
3      C        1
3      C        3
3      C        7
3      C        19
3      C        14
3      C        18

最佳答案

DROP TABLE IF EXISTS my_bad_table;
DROP TABLE IF EXISTS my_good_table;

CREATE TABLE my_bad_table
(SLNO INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,Category CHAR(1) NOT NULL
,Training_name VARCHAR(200) NOT NULL
);

INSERT INTO my_bad_table VALUES
(1,'A','1,5,9,15,12,16'),
(2,'B','2,6,10,17'),
(3,'C','1,3,7,19,14,18');

CREATE TABLE my_good_table AS
SELECT DISTINCT x.SLNO
              , x.Category
              , CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(x.training_name,',',y.i+1),',',-1) AS UNSIGNED) training_name
           FROM my_bad_table x
              , (SELECT 0 i UNION 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 8 UNION SELECT 9) y
          ORDER
             BY slno
              , category
              , training_name;

SELECT * FROM my_good_table;
+------+----------+---------------+
| SLNO | Category | training_name |
+------+----------+---------------+
|    1 | A        |             1 |
|    1 | A        |             5 |
|    1 | A        |             9 |
|    1 | A        |            12 |
|    1 | A        |            15 |
|    1 | A        |            16 |
|    2 | B        |             2 |
|    2 | B        |             6 |
|    2 | B        |            10 |
|    2 | B        |            17 |
|    3 | C        |             1 |
|    3 | C        |             3 |
|    3 | C        |             7 |
|    3 | C        |            14 |
|    3 | C        |            18 |
|    3 | C        |            19 |
+------+----------+---------------+

如果1s总是“A”等，则需要朝着标准化的方向进一步，以消除该冗余。

关于php - mysql-将列动态拆分为行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46300374/