如果没有匹配项，则选择第一条记录 [英] Select first record if none match

问题描述

在PostgreSQL中，我想根据某些条件选择一行，但是如果没有行与该条件匹配，我想返回第一行。该表实际上包含一个序数列，因此该任务应该更容易（第一行是序数为0的那一行）。例如：

  SELECT街道，邮政编码，城市
 FROM地址
 WHERE street LIKE'Test％' OR ord = 0 
 LIMIT 1； 
  

但是在这种情况下，无法保证匹配记录的顺序，我没什么可命令的。使用单个 SELECT 语句执行此操作的方式是什么？

解决方案

< blockquote>

我想根据某些条件选择一行，但是如果没有任何行
符合条件，我想返回第一行

更短（并且更正）

您实际上并不需要 WHERE 子句全部：

 选择街道，邮政编码，城市
 FROM地址
 ORDER BY street！~~'Test％'，ord 
 LIMIT 1; 
  

！~~ 只是Postgres运算符为不喜欢。您可以使用。请注意，通过反转逻辑（不喜欢而不是 Like ），我们现在可以使用默认的 ASC 排序顺序和NULL排在最后，这可能很重要。继续阅读。

这是较短的（但不一定更快）。它与当前由@Gordon接受的答案稍有不同（更可靠）。

当按布尔表达式进行排序时，您必须了解其工作方式：

• 在所有其他值之后对空值进行排序，除非特殊

当前接受的答案使用 ORDER BY< ; boolean expression> DESC ，它将首先对NULL进行排序。在这种情况下，通常应添加 NULLS LAST ：

• PostgreSQL按datetime asc排序，是否为null？

如果定义了街道 不为空这显然无关紧要，但是问题中没有定义 。 （总是提供表定义。）当前接受的答案通过在 WHERE 子句中排除NULL值来避免该问题。

其他一些RDBMS（MySQL，Oracle等）没有像Postgres这样的 boolean 类型，因此我们经常看到不正确的建议

您当前的查询（以及当前接受的答案）需要 在哪里子句-或至少 NULLS LAST 。

更重要的是，但是 ORDER BY 中的表达式不同，如果多个行具有匹配的街道（这是可以预期的），则返回的行将是任意的，并且可能在调用之间改变-通常是不希望的效果。该查询选择 ord 最小的行以打破平局并产生稳定的结果。

更加灵活，因为它不依赖于 ord = 0 的行的存在。取而代之的是，选择任一方向的 ord 最小的行。

索引较快的

（并且仍然正确。）
对于大表，以下索引可以从根本上提高此查询的性能：

 创建索引address_street_pattern_ops_idx ON地址（街道text_pattern_ops）； 
  

详细说明：

• PostgreSQL LIKE查询性能差异

根据未定义的详细信息，可能需要向索引中添加更多列。

使用该索引的最快查询：

 （
选择街道，邮政编码，城市
从地址
 WHERE street LIKE'Test％'
 ORDER BY ord –或其他方式？
-LIMIT 1-您*可以*在每条腿中添加LIMIT 1 
）
 UNION ALL 
（
选择街道，邮政编码，城市
从地址
 ORDER BY ord 
-LIMIT 1-..但是在这种情况下（b）
）$ b并没有任何改善$ b LIMIT 1 
  

BTW，这是单语句。

这比较冗长，但允许使用更简单的查询计划。如果第一个 SELECT UNION ALL 中的第二个 SELECT $ c>产生足够的行（在我们的例子中为1）。如果使用 EXPLAIN ANALYZE 进行测试，则会在查询计划中看到（从未执行）。

详细信息：

• 如何尝试多个SELECT直到结果可用？

对 UNION ALL

的评估戈登的评论。 每个文档：

多个 UNION 运算符在同一 SELECT 中除非括号中另有说明，否则对语句的评价为
从左至右。

加粗强调。

和 LIMIT 使Postgres一旦找到足够的行就停止评估。这就是为什么您在 EXPLAIN ANALYZE 的输出中看到（从未执行）的原因。

如果在最终的 LIMIT 之前添加外部 ORDER BY ，则无法进行此优化。然后必须收集所有行，以查看哪些行可能首先排序。

In PostgreSQL, I would like to select a row based on some criteria, but if no row matches the criteria, I would like to return the first row. The table actually contains an ordinal column, so the task should be easier (the first row is the one with ordinal 0). For example:

SELECT street, zip, city
FROM address
WHERE street LIKE 'Test%' OR ord = 0
LIMIT 1;

But in this case, there is no way to guarantee the order of the records that match, and I have nothing to order them by. What would be the way to do this using a single SELECT statement?

解决方案

I would like to select a row based on some criteria, but if no row matches the criteria, I would like to return the first row

Shorter (and correct)

You don't actually need a WHERE clause at all:

SELECT street, zip, city
FROM   address
ORDER  BY street !~~ 'Test%', ord
LIMIT  1;

!~~ is just the Postgres operator for NOT LIKE. You can use either. Note that by inverting the logic (NOT LIKE instead of LIKE), we can now use default ASCsort order and NULLs sort last, which may be important. Read on.

This is shorter (but not necessarily faster). It is also subtly different (more reliable) than the currently accepted answer by @Gordon.

When sorting by a boolean expression you must understand how it works:

• Sorting null values after all others, except special

The currently accepted answer uses ORDER BY <boolean expression> DESC, which would sort NULLs first. In such a case you should typically add NULLS LAST:

• PostgreSQL sort by datetime asc, null first?

If street is defined NOT NULL this is obviously irrelevant, but that has not been defined in the question. (Always provide the table definition.) The currently accepted answer avoids the problem by excluding NULL values in the WHERE clause.

Some other RDBMS (MySQL, Oracle, ..) don't have a proper boolean type like Postgres, so we often see incorrect advice from people coming from those products.

Your current query (as well as the currently accepted answer) need the WHERE clause - or at least NULLS LAST. With the different expression in ORDER BY neither is necessary.

More importantly, yet, if multiple rows have a matching street (which is to be expected), the returned row would be arbitrary and could change between calls - generally an undesirable effect. This query picks the row with the smallest ord to break ties and produces a stable result.

This form is also more flexible in that it does not rely on the existence of a row with ord = 0. Instead, the row with the smallest ord is picked either way.

Faster with index

(And still correct.) For big tables, the following index would radically improve performance of this query:

CREATE INDEX address_street_pattern_ops_idx ON address(street text_pattern_ops);

Detailed explanation:

• PostgreSQL LIKE query performance variations

Depending on undefined details it may pay to add more columns to the index.
The fastest query using this index:

(
SELECT street, zip, city
FROM   address
WHERE  street LIKE 'Test%'
ORDER  BY ord  -- or something else?
-- LIMIT 1  -- you *could* add LIMIT 1 in each leg
)
UNION ALL
(
SELECT street, zip, city
FROM   address
ORDER  BY ord
-- LIMIT 1  -- .. but that's not improving anything in *this* case
)
LIMIT  1

BTW, this is a single statement.

This is more verbose, but allows for a simpler query plan. The second SELECT of the UNION ALL is never executed if the first SELECT produces enough rows (in our case: 1). If you test with EXPLAIN ANALYZE, you'll see (never executed) in the query plan.

Details:

• Way to try multiple SELECTs till a result is available?

Evaluation of UNION ALL

In reply to Gordon's comment. Per documentation:

Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.

Bold emphasis mine.
And LIMIT makes Postgres stop evaluating as soon as enough rows are found. That's why you see (never executed) in the output of EXPLAIN ANALYZE.

If you add an outer ORDER BY before the final LIMIT this optimization is not possible. Then all rows have to be collected to see which might sort first.

这篇关于如果没有匹配项，则选择第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！