Как получить информацию о строках, которых нет в таблице? Попал в странную ситуацию

select * from table_name
where msisdn in ('msisdn1','msisdn2',......'msisdn300')

Ответ 1 · рейтинг 4 · Luke Woodward

Я думаю, что то, о чем вы просите, возможно, но требует немного усилий.

Во-первых, я не верю, что есть какой-то способ найти, какие элементы в предложении IN не соответствуют чему-либо в таблице. Поэтому вместо этого, боюсь, вам придется скопировать эту часть вашего запроса и изменить ее.

На данный момент ваш запрос выглядит примерно так

SELECT * FROM my_table WHERE id IN ('id1','id2','id3','id4','id5','id6')

Предполагая, что ни в одном из ваших MSISDN нет запятых и/или одинарных кавычек, вы можете сделать следующее:

• возьмите текст IN (...) части вашего запроса,
• удалить все одинарные кавычки,
• поставить запятую в начале и в конце,
• заключить всю строку в одинарные кавычки.

Список из шести id в моем примере выше станет тогда

',id1,id2,id3,id4,id5,id6,'

Со всеми идентификаторами в одной строке мы можем запустить такой запрос:

VARIABLE ids_string VARCHAR2(4000);
EXEC :ids_string := ',id1,id2,id3,id4,id5,id6,';

WITH comma_posns AS (SELECT level AS comma_pos
                       FROM DUAL
                      WHERE SUBSTR(:ids_string, level, 1) = ','
                       CONNECT BY LEVEL <= LENGTH(:ids_string)),
     starts_ends AS (SELECT comma_pos AS start_pos,
                            LEAD(comma_pos, 1, NULL) OVER (ORDER BY comma_pos)
                                      AS end_pos
                       FROM comma_posns),
     ids AS (SELECT SUBSTR(:ids_string, start_pos + 1, end_pos - start_pos - 1)
                    AS id
               FROM starts_ends
              WHERE end_pos IS NOT NULL)
SELECT id
  FROM ids
 WHERE id NOT IN (SELECT id FROM my_table);

Я поместил список идентификаторов в переменную связывания с именем :ids_string, чтобы я мог ссылаться на него несколько раз в запросе.

Подзапрос comma_posns перечисляет все позиции в строке, в которых появляются запятые. starts_ends затем переставляет это в пары позиций запятых, а затем ids использует эти начальную и конечную позиции, чтобы выбрать идентификаторы из строки. Наконец, мы выбираем все идентификаторы, которые есть в этой строке, но не в таблице.

Я выполнил приведенный выше запрос, используя следующие образцы данных:

SQL> select id from my_table;

ID
--------------------
id1
id1a
id4
id6

Когда я выполнил этот запрос, я получил следующий результат:

ID
--------------------------------------------------------------------------------
id2
id3
id5

Если строка вашего идентификатора особенно длинная (т. е. содержит более 4000 символов), возможно, вам придется разделить ее на части длиной 4000 или менее символов.

with to_search as (
    select 'msisdn1' as msisdn from dual
    union all
    select 'msisdn1' from dual
    union all
    select 'msisdn2' from dual
    union all
    select 'msisdn2' from dual
    ...
    (you get the picture)
    ...
    select 'msisdn3000' from dual
)
SELECT s.msisdn,
       nvl(mt.msisdn, 'not found')
FROM to_search s
  LEFT JOIN my_table mt ON mt.msisdn = s.msisdn