SQL SELECT, который исключает строки с любым из списка значений?

Я нашел много вопросов и ответов о SELECT, исключая строки со значением «НЕ В» подзапросе (например, это). Но как исключить список значений, а не подзапрос?

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

Выберите все строки ORDER, записанные между полуднем и 14:00 сегодня, за исключением тех, в которых указано это время: Сегодня 12:34, Сегодня 12:55 и Сегодня 13:05.

SQL может быть примерно таким:

SELECT * 
FROM order_
WHERE recorded_ >= ?
AND recorded_ < ?
AND recorded_ NOT IN ( list of date-times… )
;

Итак, две части этого вопроса:

  • Как написать SQL, чтобы исключить строки, имеющие любой список значений?
  • Как установить произвольное количество аргументов в PreparedStatement в JDBC?
    (произвольное число является подсчетом списка значений, которые нужно исключить)

postgresql sql jdbc where-in
person Basil Bourque    schedule 09.07.2015    source источник
comment
ваш SQL будет работать, чтобы исключить значения, как описано. stackoverflow.com/questions/178479/ отвечает НЕ В список.   -  person Norbert van Nobelen    schedule 09.07.2015
comment
Вам нужен recorded_ <> ALL( ? ) с параметром типа timestamp[]. Однако не уверен, как передать это через JDBC.   -  person Nick Barnes    schedule 09.07.2015
comment
@NickBarnes: NOT IN фактически совпадает с <> ALL(): stackoverflow.com/a/31192557/939860. Проблема с обоими: они терпят неудачу, если NULL задействован с любой стороны выражения. Выражение оценивается как NULL; но только TRUE проходит условие WHERE: stackoverflow.com/a/19528722/939860   -  person Erwin Brandstetter    schedule 09.07.2015
comment
Как объясняется в комментариях, NOT IN (...) эквивалентно <> ALL (ARRAY[...]). Вы можете создать массив в JDBC с помощью createArrayOf, а затем передать его в качестве параметра.   -  person Craig Ringer    schedule 09.07.2015

Ответы (2)


arrow_upward
3
arrow_downward

Передать массив

Быстрой и безопасной для NULL альтернативой может быть LEFT JOIN для невложенного массива:

SELECT o.*
FROM   order_ o
LEFT  JOIN unnest(?::timestamp[]) x(recorded_) USING (recorded_)
WHERE  o.recorded_ >= ?
AND    o.recorded_ <  ?
AND   x.recorded_ IS NULL;

Таким образом, вы можете подготовить один оператор и передать любое количество временных меток в виде массива.

Явное приведение ::timestamp[] необходимо только в том случае, если вы не можете ввести свои параметры (например, вы можете в подготовленные отчеты). Массив передается как один литерал text (или timestamp[]):

'{2015-07-09 12:34, 2015-07-09 12:55, 2015-07-09 13:05}', ...

Или введите CURRENT_DATE в запрос и передайте время, чтобы добавить, например, обрисовано в общих чертах @drake. Подробнее о добавлении time / interval к date:

Передать отдельные значения

Вы также можете использовать выражение VALUES или любой другой метод для создания специальной таблицы значений.

SELECT o.*
FROM   order_ o
LEFT  JOIN (VALUES (?::timestamp), (?), (?) ) x(recorded_)
                                         USING (recorded_)
WHERE  o.recorded_ >= ?
AND    o.recorded_ <  ?
AND    x.recorded_ IS NULL;

И пройти:

'2015-07-09 12:34', '2015-07-09 12:55', '2015-07-09 13:05', ...

Таким образом, вы можете передать только заданное количество временных меток.

В стороне

До 100 параметров (или ваша настройка max_function_args), вы можете использовать серверную функцию с параметром VARIADIC:

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

Связанный

person Erwin Brandstetter    schedule 09.07.2015

arrow_upward

arrow_downward

person    schedule
comment
Я использую Java и JDBC. Могу ли я сделать список в скобках просто списком экземпляров java.sql.Timestamp? Вот так: NOT IN ( ? , ? , ? ) с `preparedStatement.setTimestamp( 3 , timestampObject1 ); , повторение для 4, 5 и так далее? - person Basil Bourque; 09.07.2015