sum函数，如果存在null值，则返回null

wz8daaqr 于 2021-06-23 发布在 Mysql

关注(0)|答案(3)|浏览(572)

我有这个问题

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'

在上述情况下，预期结果为空。

sql mysql

来源：https://stackoverflow.com/questions/51736297/sum-function-to-return-null-if-null-value-exists

3条答案

按热度按时间

sf6xfgos1#

我想我可以把你的问题表述为：

SELECT SUM(CASE WHEN t1.edate >= tt1.edate THEN cost END)
FROM table1 t1 CROSS JOIN
     (SELECT edate
      FROM table1
      WHERE t1.orderNo = 'CS119484568'
     ) tt1;

那我就用 CASE 结果表达式：

SELECT (CASE WHEN SUM(t1.edate >= tt1.edate AND cost IS NOT NULL) = 0  -- no nulls
             THEN SUM(CASE WHEN t1.edate >= tt1.edate THEN cost END)
        END)
FROM table1 t1 CROSS JOIN
     (SELECT edate
      FROM table1
      WHERE t1.orderNo = 'CS119484568'
     ) tt1;

赞(0）回复(0）举报 2021-06-23

ryevplcw2#

以下是我的尝试：

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() 仅统计非空值。

赞(0）回复(0）举报 2021-06-23

lymnna713#

为此，可以使用if：

SELECT IF((SELECT COUNT(*) FROM table1 t2 WHERE t2.edate=>table1.edate AND cost IS NULL), null, (SELECT SUM(cost) FROM table1 t2 WHERE t2.edate=>table1.edate)) AS cost FROM table1 WHERE table1.orderNo = 'CS119484568'

赞(0）回复(0）举报 2021-06-23

我来回答

sum函数，如果存在null值，则返回null

3条答案

相关问题

热门标签

最新问答