MySQL：类型为NULL到0 [英] MySQL: Typecasting NULL to 0

问题描述

让我们假设下面的表（例如几个内部连接语句的结果）：

Let us suppose the following table (e.g. a result of several inner join statements):

id | column_1 | column_2
------------------------
 1 |  1       | 
 2 |  2       | 2
 3 |          | 3

您可以从以下语句获得：

Which you could for example get from the following statement:

select a.id, t1.column_1, t2.column_2
from a
left join t1 on a.id = t1.id
left join t2 on a.id = t2.id

现在，如果我想如下所示汇总t1.column_1和t2.column_2：

Now, if i'd like to sum up t1.column_1 and t2.column_2 as follows

select 
    a.id, 
    t1.column_1, 
    t2.column_2,
    (t1.column_1 + t2.column_2) as cumulated
from a
left join t1 on a.id = t1.id
left join t2 on a.id = t2.id

reslut将如下所示：

The reslut will look as follows:

id | column_1 | column_2 | cumulated
------------------------------------
 1 |  1       | NULL     | NULL
 2 |  2       | 2        | 4
 3 |  NULL    | 3        | NULL

我的问题基本上是：有一种方法将类型转换为0为了做一些数学？

My question basically is: is there a way to typecast NULL into 0 in order to do some math?

我尝试过 CONVERT（t1.column_1，SIGNED）和 CAST column_1 as SIGNED），但 NULL 保留 NULL 。 b $ b

I have tried CONVERT(t1.column_1, SIGNED) and CAST(t1.column_1 as SIGNED), but a NULL stays a NULL.

推荐答案

使用 IFNULL（column，0）将列值转换为零。或者，COALESCE函数将执行相同的操作，除了（1） COALESCE 符合ANSI， IFNULL ，和（2） COALESCE 获取任意数量的列/值，并返回传递给它的第一个非空值。

Use IFNULL(column, 0) to convert the column value to zero. Alternatively, the COALESCE function will do the same thing, except (1) COALESCE is ANSI-compliant, IFNULL is not, and (2) COALESCE takes an arbitrary number of columns/values and will return the first non-null value passed to it.

这篇关于MySQL：类型为NULL到0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！