如果MySQL不为空，则显示1，否则显示0

I'm working with a little display complication here. I'm sure there's an IF/ELSE capability I'm just overlooking.

我正在处理一个显示复杂的问题。我确信我忽略了一个IF/ELSE功能。

I have 2 tables I'm querying (customers, addresses). The first has the main record, but the second may or may not have a record to LEFT JOIN to.

我要查询两个表(客户、地址)。第一个有主记录，但是第二个可能有也可能没有留下连接的记录。

I want to display a zero if there is no record in the addresses table. And I want to only display 1, if a record exists.

如果地址表中没有记录，我想显示一个0。如果记录存在，我只想显示1。

What I've attempted so far:

到目前为止我所尝试的:

SELECT c.name, COALESCE(a.addressid,0) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123

This first example does not do it. But I may be utilizing COALESCE wrong.

第一个示例没有这样做。但我可能用错了煤层。

How can I display a 0, if null, and a 1, if something exists?

如果存在0和1，如何显示0 ?

5 个解决方案

#1

164

Instead of COALESCE(a.addressid,0) AS addressexists, use CASE:

用例:

CASE WHEN a.addressid IS NOT NULL 
       THEN 1
       ELSE 0
END AS addressexists

or the simpler:

或者更简单的:

(a.addressid IS NOT NULL) AS addressexists

This works because TRUE is displayed as 1 in MySQL and FALSE as 0.

这是因为在MySQL中TRUE显示为1,FALSE显示为0。

#2

SELECT c.name, IF(a.addressid IS NULL,0,1) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123

#3

Careful if you're coming from C/C++ and expecting this to work:

如果你来自C/C++，并期望它能起作用，请小心:

select if(name, 1, 0) ..

Even if 'name' is not NULL, unlike in C, a false-condition still triggers and the above statement returns 0. Thus, you have to remember to explicitly check for NULL or empty string:

即使“name”不是NULL(不像C中那样)，错误条件仍然会触发，上面的语句返回0。因此，您必须记住显式地检查空字符串或空字符串:

 select if(name is null or name = '', 0, 1)

PS Eugen's example up above is correct, but I wanted to clarify this nuance as it caught me by surprise.

上列的PS Eugen的例子是正确的，但是我想澄清一下这个细微的差别，因为它让我吃惊。

#4

SELECT 
    c.name, 
    CASE WHEN a.addressid IS NULL THEN 0 ELSE 1 END AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123

#5

Another method without WHERE, try this..

另一种方法没有地方，试试这个。

Will select both Empty and NULL values

是否同时选择空值和空值

SELECT ISNULL(NULLIF(fieldname,''))  FROM tablename

It will set null if it is an empty string, then be true on that also.

如果它是一个空字符串，它将设置为null，那么它也为true。

#1

164