MySQL IF NOT NULL，然后显示1，否则显示0 [英] MySQL IF NOT NULL, then display 1, else display 0

问题描述

我正在处理一个小显示复杂功能。我确信我只是忽略了IF / ELSE功能。

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

我有2个表我正在查询（客户，地址）。第一个有主记录，但第二个可能有也可能没有LEFT JOIN到的记录。

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.

如果没有记录，我想显示零地址表。
我想只显示1，如果有记录。

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.

到目前为止我尝试过：

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

第一个例子没有这样做。但我可能错误地使用了COALESCE。

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

如果存在空值，我如何显示0，如果存在，则如何显示1？

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

推荐答案

使用 CASE而不是 COALESCE（a.addressid，0）AS addressexists ：

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

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

或更简单：

(a.addressid IS NOT NULL) AS addressexists

这是有效的，因为 TRUE 在MySQL中显示为 1 并且 FALSE as 0 。

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

这篇关于MySQL IF NOT NULL，然后显示1，否则显示0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！