Блог

Привет, у меня есть этот пример строк из моей базы данных, моя цель — разделить значение столбца на отдельные столбцы по 5, и если значение отсутствует для других столбцов, то оно должно возвращать 0 в качестве значения

 Row1
ColumnID: 1
Column Value: "1,2,3"

Row2
ID: 2
Column Value: "1,2,3,4,5"
  

Моя цель — получить это

 Row1
ColumnID: 1
Column Level1: 1
Column Level2: 2
Column Level3: 3
Column Level4: 0
Column Level5: 0

Row2
ID: 2
Column Level1: 1
Column Level2: 2
Column Level3: 3
Column Level4: 4
Column Level5: 5
  

Но сгенерированный результат таков

 Row1
ColumnID: 1
Column Level1: 1
Column Level2: 2
Column Level3: 3
Column Level4: 3
Column Level5: 3

Row2
ID: 2
Column Level1: 1
Column Level2: 2
Column Level3: 3
Column Level4: 4
Column Level5: 5
  

Это мой текущий запрос

 SELECT  
    a.columnval,
    SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 1), ',', -1) as column1, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 2), ',', -1) as column2, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 3), ',', -1) as column3, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 4), ',', -1) as column4, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 5), ',', -1) as column5 
FROM mytable a
  

Однако, если у меня есть строка «1,2,3»
Получение столбцов 1, 2, 3 не является проблемой, проблема в том, что если строка, которая у меня есть, имеет только «1,2», когда она пытается получить столбец 3, возвращаемое значение равно 2, при этом оно должно возвращать 0, потому что столбец 3 для строки «1,2» отсутствует.

Я думаю, что мне нужно определить длину или проверить длину перед получением значений столбца, но я не знаю, как это сделать в MYSQL

 WITH 
cte1 AS ( SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5),
cte2 AS ( SELECT id FROM test ),
cte3 AS ( SELECT test.id, jsontable.*
          FROM test
          CROSS JOIN JSON_TABLE( CONCAT('["', REPLACE(test.value, ',', '","'), '"]'),
                                 "$[*]" COLUMNS( num FOR ORDINALITY,
                                                 value VARCHAR(254) PATH "$" )
                               ) AS jsontable )
SELECT cte2.id ColumnID, 
       cte1.num ColumnLevel, 
       COALESCE(cte3.value, 0) value
FROM cte1
CROSS JOIN cte2
LEFT JOIN cte3 ON cte1.num = cte3.num
              AND cte2.id = cte3.id
ORDER BY cte2.id, cte1.num
  

или

 WITH 
cte1 AS ( SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5),
cte2 AS ( SELECT id, num 
          FROM test
          CROSS JOIN cte1 ),
cte3 AS ( SELECT test.id, jsontable.*
          FROM test
          CROSS JOIN JSON_TABLE( CONCAT('["', REPLACE(test.value, ',', '","'), '"]'),
                                 "$[*]" COLUMNS( num FOR ORDINALITY,
                                                 value VARCHAR(254) PATH "$" )
                               ) AS jsontable )
SELECT cte2.id ColumnID, 
       cte2.num ColumnLevel, 
       COALESCE(cte3.value, 0) value
FROM cte2
LEFT JOIN cte3 USING (id, num)
ORDER BY cte2.id, cte2.num
  

скрипка

Требуется MySQL версии 8.0.4 или новее.

Следующий запрос должен работать:

 SELECT  
    a.id,
    case 
       when tmplen < 1
       then 0
       else
            SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 1), ',', -1)
    end as column1,
    case 
       when tmplen < 2
       then 0
       else
            SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 2), ',', -1)
    end as column2,
    case 
       when tmplen < 3
       then 0
       else
            SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 3), ',', -1)
    end as column3,
    case 
       when tmplen < 4
       then 0
       else
            SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 4), ',', -1)
    end as column4,
    case 
       when tmplen < 5
       then 0
       else
            SUBSTRING_INDEX(SUBSTRING_INDEX(single_path, ',', 5), ',', -1)
    end as column5
FROM mytable a 
inner join
   (select id, 
           LENGTH(single_path) - LENGTH(REPLACE(single_path, ',', ''))   1 as tmpLen
    from mytable
   ) t
on t.id = a.id
  

Пожалуйста, найдите демо здесь.

Здесь, с LENGTH(single_path) - LENGTH(REPLACE(single_path, ',', '')) 1 , я не вычисляю количество элементов в каждой single_path записи и соединяю ее с исходной таблицей на основе id .

При получении результатов я использую это, tmpLen (no of items for each record) чтобы решить, отображать substring или 0 в качестве выходных данных.