Подтвердить что ты не робот

MySQL - выберите из списка номеров те, у которых нет аналога в поле id таблицы

У меня есть список чисел, например {2,4,5,6,7} У меня есть таблица foos, с foos.ID, включая, скажем, {1,2,3,4,8,9}

Мне нравится принимать мой список чисел и находить те, у кого нет аналога в поле ID моей таблицы.

Одним из способов достижения этой цели было бы создание табличных таблиц, загруженных {2,4,5,6,7} в поле ID. Тогда я бы сделал

SELECT bars.* FROM bars LEFT JOIN foos ON bars.ID = foos.ID WHERE foos.ID IS NULL

Однако, я бы хотел выполнить эту таблицу sans temp.

Есть ли какие-либо данные о том, как это может произойти?

4b9b3361

ОТВЕТЫ

Ответ 1

Это довольно распространенная проблема: создание отношения "на лету" без создания таблицы. SQL-решения для этой проблемы довольно неудобны. Один пример с использованием производной таблицы:

SELECT n.id
FROM
  (SELECT 2 AS id 
   UNION SELECT 3 
   UNION SELECT 4 
   UNION SELECT 5 
   UNION SELECT 6 
   UNION SELECT 7) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

Но это не очень хорошо масштабируется, потому что у вас может быть много значений вместо шести. Может оказаться утомительным построить длинный список с одним UNION необходимым значением.

Другое решение состоит в том, чтобы держать таблицу общего назначения с десятью цифрами под рукой и использовать ее несколько раз для нескольких целей.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT n.id
FROM 
  (SELECT n1.i + n10.i*10 AS id
   FROM num AS n1 CROSS JOIN num AS n10
   WHERE n1.i + n10.i*10 IN (2, 3, 4, 5, 6, 7)) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

Я показываю внутренние запросы, генерирующие значения от 0..99, даже если это не является необходимым для этого случая. Но у вас могут быть значения больше 10 в вашем списке. Дело в том, что с одной таблицей num вы можете генерировать большие числа, не прибегая к очень длинным цепям с одним UNION за значение. Кроме того, вы можете указать список желаемых значений в одном месте, что более удобно и читаемо.

Ответ 2

Я не могу найти решение вашей точной проблемы, которая не использует временную таблицу, но альтернативный способ выполнения вашего запроса с помощью подвыборки вместо объединения:

SELECT bars.* FROM bars WHERE bars.ID NOT IN (SELECT ID FROM foos)

Как и другие плакаты, я изначально писал:

SELECT * FROM foos WHERE foos.ID NOT IN (2, 4, 5, 6, 7)

но потом я понял, что это создает противоположность тому, что вы хотите.

Ответ 3

Если вы используете PHP, вы можете сделать эту работу без создания временных таблиц.

SELECT ID FROM foos WHERE foos.ID IN (2, 4, 5, 6, 7)

Вы можете использовать функцию PHP array_diff(), чтобы преобразовать это в желаемый результат. Если ваш список (2,4,5,6,7) находится в массиве с именем $list, и результат запроса выше в массиве $result, то

$no_counterparts = array_diff($list, $result);

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

Ответ 4

У меня была аналогичная проблема. У меня был диапазон, в котором у первичного ключа с автоинкрементами были некоторые отсутствующие значения, поэтому сначала я нашел, сколько их было: select count(*) from node where nid > 1962. Сравнивая это число с наивысшим значением, я получил номер, отсутствующий. Затем я выполнил этот запрос: select n2.nid from node n1 right join node n2 on n1.nid = (n2.nid - 1) where n1.nid is null and n2.nid > 1962 Это найдет количество непоследовательных отсутствующих записей. Он не будет показывать последовательные, и я не совсем уверен, как это сделать, кроме изменения предложения ON, чтобы обеспечить большую широту (что значительно увеличило бы таблицу JOIN). Во всяком случае, это дало мне пять результатов из семи отсутствующих, а два других гарантировали быть рядом с хотя бы одним из пяти. Если у вас больше недостающего количества, вам, вероятно, понадобится другой способ найти оставшиеся недостающие.

Ответ 5

Решение Alnitak (и ваше) должно работать, и я не могу ничего о чем-либо еще, что может работать только на языке SQL.

Но вот вопрос: как вы передаете список значений? Разве не лучше справиться с этим в вызывающем коде, т.е. Запросить идентификатор и сравнить его в коллинг-коде, который может быть на языке, который лучше подходит для такого рода манипуляций.