#sql #database #sqlite #recursive-query #group-concat
#sql #База данных #sqlite #рекурсивный запрос #группа-объединение
Вопрос:
У меня есть таблица с текстовым столбцом в базе данных, где записи для каждой строки записываются в виде списков, например, столбца test
:
[['a','1'],['b','2'],['c','3']]
[['a','2'],['a','4'],['c','8'],['d','3']
[['b','3'],['c',5'],['c','6'],['d','4']]
Я хочу извлечь значения в три новых столбца x y z
, где
- x содержит все значения a и b;
- y содержит все значения c;
- z содержит все значения d, например
Вот так:
x y z
['a','1'],['b','2'] ['c','3']
['a','2'],['a','4'] ['c','8'] ['d','3']
['b','3'] ['c','5'],['c','6'] ['d','4']
Возможно ли это сделать с помощью инструкции SQL?
Заранее спасибо
Комментарии:
1. Пометьте свой вопрос с помощью базы данных, которую вы используете.
Ответ №1:
Вам нужен рекурсивный CTE для разделения каждой строки, а затем GROUP_CONCAT() для сбора значений в соответствии с вашими требованиями:
WITH cte AS (
SELECT rowid,
SUBSTR(test, 1, INSTR(test || ';', ';') - 1) col,
SUBSTR(test, INSTR(test || ';', ';') 1) rest
FROM (SELECT rowid, REPLACE(REPLACE(REPLACE(test, '[[', '['), ']]', ']'), '],[', '];[') test FROM tablename)
UNION ALL
SELECT rowid,
SUBSTR(rest, 1, INSTR(rest || ';', ';') - 1),
SUBSTR(rest, INSTR(rest || ';', ';') 1)
FROM cte
WHERE LENGTH(rest) > 0
)
SELECT GROUP_CONCAT(CASE WHEN col LIKE '[''a'',%' OR col LIKE '[''b'',%' THEN col END) x,
GROUP_CONCAT(CASE WHEN col LIKE '[''c'',%' THEN col END) y,
GROUP_CONCAT(CASE WHEN col LIKE '[''d'',%' THEN col END) z
FROM cte
GROUP BY rowid
Посмотрите демонстрационную версию.
Результаты:
x | y | z |
---|---|---|
[‘a’,’1′],[‘b’,’2′] | [‘c’,’3′] | нулевой |
[‘a’,’2′],[‘a’,’4′] | [‘c’,’8′] | [‘d’,’3′] |
[‘b’,’3′] | [‘c’,5′],[‘c’,’6′] | [‘d’,’4′] |
Комментарии:
1. большое спасибо! Я просто исправляю это для себя (тест, ‘[[‘, ‘[‘), ‘]]’, ‘]’), ‘], [‘, ‘];[‘). Я добавляю пробел между этими ‘], [‘ и работает очень хорошо для меня.
2. @enzococca в ваших образцах данных я не вижу пробела между
,
и[
, но если это сработало, тогда все в порядке.3. вы правы, но в моих реальных данных у меня есть! еще раз спасибо. Лучшие
4. просто вопрос, может быть, работает тот же запрос для postgresql?
5. @enzococca поскольку это не так. Синтаксис отличается: dbfiddle.uk /…