如何将COALESCE的值与NULL值相加

Question

我想知道是否有可能在合并中使用的值，即使其中一些是空的。目前，如果coalesce中的任何值返回null，则求和的结果也为null。

下面是一个SQL的基本情况:http://sqlfiddle.com/!17/9eecb/83134

CREATE TABLE IF NOT EXISTS table1 (
item_a INT,
item_b INT,
item_c INT
);
CREATE TABLE IF NOT EXISTS table2 (
item_a INT,
item_b INT,
item_c INT
);
INSERT INTO table1(item_a, item_b, item_c)
VALUES (3, NULL, NULL);
INSERT INTO table2(item_a, item_b, item_c)
VALUES (NULL, 2, NULL);
SELECT (NULLIF(COALESCE (t1.item_a, t2.item_a),0) +
NULLIF(COALESCE (t1.item_b, t2.item_b),0) +
NULLIF(COALESCE (t1.item_c, t2.item_c),0)) as item_sum,
COALESCE (t1.item_a, t2.item_a) as item_a,
COALESCE (t1.item_b, t2.item_b) as item_b,
COALESCE (t1.item_c, t2.item_c) as item_c
FROM table1 t1, table2 t2

前面的查询应该返回5作为item_sum(3 + 2).然而，两个表中的最后一列item_c都是null，所以结果是null而不是5。

感谢

Answer 1

COALESCE

COALESCE函数返回第一个不是的参数null。只有当所有参数都为空时才返回Null。它经常是用于在数据为时用默认值替换空值检索显示

SELECT COALESCE (t1.item_a, t2.item_a, 0) +
COALESCE (t1.item_b, t2.item_b, 0) +
COALESCE (t1.item_c, t2.item_c, 0) as item_sumA,
COALESCE (t1.item_a, t2.item_a) as item_a,
COALESCE (t1.item_b, t2.item_b) as item_b,
COALESCE (t1.item_c, t2.item_c) as item_c
FROM table1 t1, table2 t2

小提琴