SQL SELECT 排除具有任何值列表的行？

Question

我发现了很多关于 SELECT 的问题和解答，其中排除了值为“NOT IN”的行的子查询(例如 this)。但是如何排除值列表而不是子查询？

我想搜索时间戳在一个范围内但排除某些特定日期时间的行。在英语中，这将是:

Select all the ORDER rows recorded between noon and 2 PM today except for the ones of these times: Today 12:34, Today 12:55, and Today 13:05.

SQL 可能是这样的:

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

这个问题分为两部分:

• 如何编写 SQL 以排除具有任何值列表的行？
• 如何在 JDBC 中为 PreparedStatement 设置任意数量的参数？
  (任意数量是要排除的值列表的数量)

Answer 1

传递数组

一个快速且 NULL 安全的替代方法是对未嵌套数组进行 LEFT JOIN:

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

这样您就可以准备一条语句并将任意数量的时间戳作为数组传递。

显式转换 ::timestamp[] 仅在您无法键入参数时才有必要(就像您可以在 prepared statements 中那样)。该数组作为单个 text(或 timestamp[])文字传递:

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

或者将 CURRENT_DATE 放入查询中并传递时间以添加 outlined by @drake .有关将 time/interval 添加到 date 的更多信息:

• How to get the end of a day?

传递个别值

您还可以使用 VALUES 表达式 - 或任何其他方法来创建临时值表。

SELECT o.*
FROM   order_ o
<b>LEFT  JOIN (VALUES (?::timestamp), (?), (?) ) x(recorded_)
                                         USING (recorded_)</b>
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 参数的服务器端函数:

• Return rows matching elements of input array in plpgsql function

我知道您知道 timestamp 特性，但对于一般公众:等式匹配对于时间戳可能很棘手，因为它们最多可以有 6 个小数位表示秒，您需要匹配正是。

相关

• Select rows which are not present in other table
• Optimizing a Postgres query with a large IN