本文介绍了如果不存在记录,如何选择 sum -or- 0?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要编写一个查询,返回满足某个条件的所有值的总和,但是如果没有找到任何行,则该查询需要返回 0,而不是 null.例如:
I need to write a query that returns the sum of all values that meet a certain criteria, but the query needs to return 0 if no rows are found, rather than null. For example:
tab +---------------+-----+ | descr | num | +---------------+-----+ | hello there | 5 | | hi there | 10 | | hello | 10 | | hi there! | 15 | +---------------+-----+这个查询:
SELECT sum(num) AS val FROM tab WHERE descr LIKE "%hello%";应该并且确实返回 15.然而:
should, and does, return 15. However:
SELECT sum(num) AS val FROM tab WHERE descr LIKE "%greetings%";应该返回0,但确实返回null.
should return 0, but does return null.
有人可以解释一下吗?
推荐答案怎么样:
SELECT COALESCE(sum(num), 0) AS val FROM tab WHERE descr LIKE "%greetings%";COALESCE 函数基本上说返回第一个参数,除非它是 null 在这种情况下返回第二个参数" - 在这些情况下非常方便.
The COALESCE function basically says "return the first parameter, unless it's null in which case return the second parameter" - It's quite handy in these scenarios.
更多推荐
如果不存在记录,如何选择 sum
发布评论