MySQL查询-如果找不到条目，​​如何返回占位符值而不是NULL [英] MySQL query - how to return placeholder value instead of NULL if no entry found

问题描述

假设我有一个名为 references 的表，它有两个字段:id 和 reference 字段.

Let's say I have a table called references, which has two fields: an id and a reference field.

我想创建一个查询，根据 ID 为我提供参考号.像这样:

I want to create a query that will provide me with a reference number based upon an id. Like this:

SELECT reference 
  FROM references 
 WHERE id = x 

(其中 x 是某个整数)

但是，如果在表中找不到 id，我希望查询显示 -1 而不是 NULL.

However if the id is not found in the table I would like the query to show -1 instead of NULL.

我该怎么做?

SELECT COALESCE(reference, -1) FROM references WHERE id = x 

不起作用

推荐答案

如果子集具有 cardinality 0(id = 2 的元素)，则没有什么可比较的，这样 (id =2) 元素不存在.另一方面，如果你想找到空子集中的最大元素，你会得到未知值(超集的每个成员都是空集的上界和下界)

if subset has cardinality 0 (elements with id = 2), there is nothing to compare, there's certainity that such (id = 2) element doesn't exist. in the other hand, if you want to find, let's say, maximum element in that empty subset, you will get unknown value (every member of superset will be a supremum and infimum of the empty set)

我不确定它是否正确，但恕我直言，很合逻辑

i'm not sure if it's correct, but imho, quite logical

这篇关于MySQL查询-如果找不到条目，​​如何返回占位符值而不是NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！