文章目录
1.COUNT(expr)
Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.
- count(*):不忽略NULL值,有几行算几行;
- count(num):如果字段值为NULL,该行忽略不统计。
因此,针对某个字段进行统计时,如果该字段可能为NULL就要注意了,这可能不是你想要的结果。
2.SUM([DISTINCT] expr)
If there are no matching rows, SUM() returns NULL.
SUM函数,会忽略NULL值,对非NULL值求和。
但需要注意的是,如果没查到数据,或字段全为NULL,则SUM函数返回NULL。
为避免出现空指针,一般我们希望的是返回0。解决方法如下:
select coalesce(sum(num),0)...
or
select ifnull(sum(num),0)...
3.COALESCE(value,…)
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
The return type of COALESCE() is the aggregated type of the argument types.
示例:
SELECT COALESCE(NULL,1); -- 1
SELECT COALESCE(2,NULL); -- 2
SELECT COALESCE(NULL,3,2); -- 3
SELECT COALESCE(NULL,NULL,NULL); -- NULL
4.IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
示例:
SELECT IFNULL(1,0); -- 1
SELECT IFNULL(NULL,10); -- 10
SELECT IFNULL(1/0,10); -- 10
SELECT IFNULL(1/0,'yes'); -- 'yes'
5.If there are no matching rows
- AVG() returns NULL.
- COUNT() returns 0.
- COUNT(DISTINCT) returns 0.
- MAX() returns NULL.
- MIN() returns NULL.
- SUM() returns NULL.
参考
https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html
https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce
https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_ifnull
---转载本站文章请注明作者和出处 二进制之路(binarylife.icu),请勿用于任何商业用途---