mysql - 如果存在 null 值，SUM 函数将返回 null

Question

我有这个查询

SELECT (SELECT cost FROM table1 t2 WHERE t2.edate=>table1.edate) AS cost
FROM table1
WHERE table1.orderNo = 'CS119484568'

以上查询返回:

cost
4
3
null
null
5

如果任何行有空值，我想获得全部的总和或空值

SELECT SUM((SELECT cost FROM table1 t2 WHERE t2.edate=>table1.edate)) AS cost
FROM table1
WHERE table1.orderNo = 'CS119484568'

上述情况下的预期结果为 NULL。

Answer 1

这是我的尝试:

SELECT
    (SELECT
        case when count(*)=count(cost) then sum(cost) else null end
     FROM table1 t2
     WHERE t2.edate=>table1.edate
    ) AS cost
FROM table1
WHERE table1.orderNo = 'CS119484568'

这是基于 count() 仅计算非空值这一事实。