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

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

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

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

ВЫБРАТЬ бары.* ИЗ баров ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ к foos ON bars.ID = foos.ID ГДЕ foos.ID НЕДЕЙСТВИТЕЛЕН

Тем не менее, я хотел бы выполнить эту таблицу без временной.

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

Источник

6 ответов

Это довольно распространенная проблема: создание отношения на лету без создания таблицы. Решения 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 за значение. Также вы можете указать список желаемых значений в одном месте, что более удобно и читабельно.

Источник

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

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)

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

Источник

Если вы используете 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, минимальна, чтобы получить то, что вы хотите, и может быть целесообразно избежать создания временной таблицы.

Источник

У меня была похожая проблема. У меня был диапазон, в котором у первичного ключа с автоинкрементом были некоторые пропущенные значения, поэтому сначала я нашел, сколько их было: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). В любом случае, это дало мне пять результатов из семи пропавших без вести, а два других гарантированно оказались рядом хотя бы с одним из пяти. Если у вас пропущено большее число, вам, вероятно, понадобится другой способ найти пропущенное.

Источник

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

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

Источник

Случайно попал сюда в поисках ответа. Предыдущие сообщения были написаны до MySQL 8. Поскольку в MySQL есть операторы начиная с версии 8.0.19, вопрос можно очень элегантно решить, используяvalueзаявление вместе сCTEкоторый также доступен начиная с MySQL 8.0.

Шаг 1. Объедините CTE и оператор значения, чтобы создать набор строк со значениями, которые необходимо сравнить с таблицей (здесь таблица).

      with MyValues(val) as
(
  values row(2),row(4),row(5),row(6),row(7)
)

Шаг 2. Внешнее соединение CTE с таблицей и фильтрация строк, которые имеют нулевые значения из CTE после внешнего соединения с помощьюfoo

      WITH myvalues(val)
     AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
SELECT f.id
FROM   foo f
       LEFT OUTER JOIN myvalues m
                    ON f.id = m.val
WHERE  m.val IS NULL;

Тащить

      mysql> WITH myvalues(val)
    ->      AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
    -> SELECT f.id
    -> FROM   foo f
    ->        LEFT OUTER JOIN myvalues m
    ->                     ON f.id = m.val
    -> WHERE  m.val IS NULL;
+------+
| id   |
+------+
|    1 |
|    3 |
|    8 |
|    9 |
+------+
4 rows in set (0.00 sec)

Или используя предложение IN

      mysql> WITH myvalues(val)
    ->      AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
    -> SELECT f.id
    -> FROM   foo f
    -> WHERE  id NOT IN (SELECT val
    ->                   FROM   myvalues);
+------+
| id   |
+------+
|    1 |
|    3 |
|    8 |
|    9 |
+------+
4 rows in set (0.00 sec)
Источник
Другие вопросы по тегам