Я хочу предоставить запрос в свою базу данных, который будет искать, существует ли строка в Column2 (site_id), а затем поместить эту строку в новую таблицу со значением ее предыдущего столбца в этой строке.
таблица имеет только 2 столбца, а столбец site_id может содержать много строк из 5 слов, которые мне нужны.
в приведенном ниже примере я хочу получить все идентификаторы конкретных сайтов. например: E7089 или E7459 (мне нужны все из них, и первое слово случайное, например, E или T или т. д., а четыре цифры можно изменить)
текущая первая строка выглядит так: с одним ticket_id и многими site_ids (мне нужны только идентификаторы сайтов, такие как: g1231 или g1236, а не адреса в скобках)
ticket_id | site_id |
sss-bb-12312312-12312 | g1231(afsdgf-sdgsdgdg), g1236(sdfsdgsdg), g3212(asdfas-dfsd), b2311(asdasd), b3213(asdfsdf)
и сделать так:
ticket_id | site_id |
sss-bb-12312312-12312 g1231
sss-bb-12312312-12312 g3211
sss-bb-12312312-12312 g1236
sss-bb-12312312-12312 b2311
sss-bb-12312312-12312 b3213
я уже могу выполнить поиск по всему второму столбцу и найти идентификатор сайта из 5 слов с регулярным выражением (если вы хотите попробовать: [A-Z]\d{1,4}), но я не могу извлечь их из строки и вставить каждый из их в новую строку, мой текущий код выглядит так:
drop TABLE if EXISTS test2;
CREATE TABLE if NOT EXISTS test2 (
Ticket_id varchar,
site_id varchar
);
INSERT INTO test2
SELECT ticket_id, site_id
FROM TEST WHERE site_id regexp '[A-Z]\d{1,4}';
но приведенный выше код найдет строку с идентификатором site_id и вставит все строки, которые будут соответствовать поиску, а я этого не хочу. может кто-нибудь помочь преобразовать первое во второе? в основном текущая БД выглядит так:
culumn1 | column2
ticket1 | many site ids
ticket2 | many site ids
но я хочу так:
culumn1 | column2
ticket1 | id
ticket1 | id
ticket1 | id
ticket1 | id
ticket2 | id
ticket2 | id
ticket2 | id
- билеты не требуют каких-либо изменений, кроме копирования в новые строки с назначенным им site_id
- есть много идентификаторов site_id для каждого билета (я уже могу найти их с помощью регулярного выражения), которые необходимо разделить на новые строки, как указано выше.
- это нужно делать только в браузере sqlite db и браузере db (он назначен так и должен быть сделан так, поэтому, к сожалению, нет python)
Вам нужен рекурсивный CTE для разделения столбца site_id
таблицы test1
и функция SUBSTR(), чтобы взять первые 5 символов для вставки в таблицу test2
:
WITH cte AS (
SELECT ticket_id, '' site_id, site_id || ',' s
FROM test1
UNION ALL
SELECT ticket_id,
SUBSTR(s, 0, INSTR(s, ',')),
SUBSTR(s, INSTR(s, ',') + 1)
FROM cte
WHERE s <> ''
)
INSERT INTO test2 (ticket_id, site_id)
SELECT ticket_id, SUBSTR(TRIM(site_id), 1, 5)
FROM cte
WHERE site_id <> '';
Смотрите демо.
Спасибо, чувак, ты бог @forpas