mysql - mysql中将表列拆分为多列

Question

我的表格如下所示(我从电子表格填充它)；为了方便操作，我需要将函数代码 AA 列分成多个列。该列中的值用 , 连接。用逗号分隔值将它们分开。

Eg:
Function CodeAA1    Function CodeAA2  ..
03                    04             ..

我怎样才能在mysql中做到这一点？

Mysql工作台版本6.3对那些逗号分隔的列没有限制。我预计它会从1到10。

Answer 1

“字数”计数、CASE 和 SUBSTRING_INDEX 的某种组合应该会得到您预期的结果。如下所示:

SELECT fcaa,
       CASE WHEN t_cnt >= 1 THEN SUBSTRING_INDEX(fcaa, "," ,1) END AS Fcaa1,
       CASE WHEN t_cnt >= 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(fcaa, "," ,2), "," ,-1) END AS Fcaa2,
       CASE WHEN t_cnt >= 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(fcaa, "," ,3), "," ,-1) END AS Fcaa3,
       CASE WHEN t_cnt >= 4 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(fcaa, "," ,4), "," ,-1) END AS Fcaa4,
       CASE WHEN t_cnt >= 5 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(fcaa, "," ,5), "," ,-1) END AS Fcaa5,
       CASE WHEN t_cnt >= 6 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(fcaa, "," ,6), "," ,-1) END AS Fcaa6,
       CASE WHEN t_cnt >= 7 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(fcaa, "," ,7), "," ,-1) END AS Fcaa7,
       CASE WHEN t_cnt >= 8 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(fcaa, "," ,8), "," ,-1) END AS Fcaa8,
       CASE WHEN t_cnt >= 9 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(fcaa, "," ,9), "," ,-1) END AS Fcaa9,
       CASE WHEN t_cnt >= 10 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(fcaa, "," ,10), "," ,-1) END AS Fcaa10
FROM
(SELECT `yourColumn` AS fcaa,
        (LENGTH(`yourColumn`)-LENGTH(REPLACE(`yourColumn`, "," ,'')))+1 AS t_cnt 
 FROM   yourTable) A;

1. 第一部分是计算列行中有多少个“单词”。因此，使用整个LENGTH() 数据减去LENGTH()(不带空格)。我们在末尾添加 1，因为如果您看到示例 '03,04,07,08,12,13' 之一，它出现了 6 次('word')，但如果我们这样做 (LENGTH(yourColumn)-LENGTH(REPLACE(yourColumn,',',''))) 如果没有 +1，我们只会得到 5 个计数。

2. 第二部分是将 CASE 与 SUBSTRING_INDEX 结合使用。因此，当 count('t_cnt' - 字数) 匹配某个值时，我们使用相应的 SUBSTRING_INDEX 函数返回该值。

我确信有更好的方法可以做到这一点，但我之前碰巧做过同样的事情，这个方法看起来很长，但它对我有用。希望它也对您有用。