目录
SUM 函数可以对单个列求和,也可以对多个列运算后求和,计算中忽略 NULL 值
SUM对多个列运算后求和,如果运算的列中有个字段为 NULL, 运算结果也为 NULL
在没有符合条件的数据时使用 SUM 函数,则 SUM 函数返回 NULL,而不是 0
可以通过结合使用 IFNULL 或 COALESCE 函数来解决结果为 NULL 的情况
3.使用如果表达式IF(expr, v1, v2), expr 成立,返回结果 v1;否则,返回结果 v2
一、前期准备工作
本文基于自己的理解,总结了 SUM() 函数的用法。为了更加直观,我先新建一个 student_table 表并往表里面插入一些数据,方便后面的 sql 举例。如有错误,还请指出。
新建表如下:
CREATE TABLE `student_table` ( `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键', `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名', `gender` int DEFAULT '0' COMMENT '性别 0:保密 1:男 2:女', `id_card` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '身份证号', `national` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '民族', `phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系电话', `score` decimal(10,2) DEFAULT NULL COMMENT '期末总分数', `describe` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生表';
插入的数据如下:
insert into student_table values(1,'小明','1','111222333444555666','汉族','11122233344','8','zxcvbnm'); insert into student_table values(2,'红红','2','999999999444444444','汉族','99999444444','7','sfsdvsvs'); insert into student_table values(3,'张三','0','666666666888888888','苗族','66666688888','8','gsfwwsfg'); insert into student_table values(4,'李四','0','111111111444444444','苗族','11111144444','6','erefsss'); insert into student_table values(5,'王五','0','333333333777777777','苗族','33333377777','4','lkjgdgsgssftg'); insert into student_table values(6,'小绿',NULL,NULL,NULL,NULL,NULL,NULL); insert into student_table values(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
二、SUM()函数的用法
1.求和,返回指定列的总和
1.
SUM 函数可以对单个列求和,也可以对多个列运算后求和,计算中忽略 NULL 值(如果运算的列中有个字段为 NULL, 运算结果也为 NULL)
SUM函数与分组 GROUP BY 使用,可以分组求和
SUM函数与分组 GROUP BY 使用:SUM对多个列运算后求和,如果运算的列中有个字段为 NULL, 运算结果也为 NULL
(最后一个结果为null: id 不为 NULL,但是 score 为 NULL ,运算的结果也为 NULL )
2.
在没有符合条件的数据时使用 SUM()函数,或者当某一列全是NULL时,则 SUM()函数返回 NULL,而不是 0
可以通过结合使用 IFNULL 或 COALESCE 函数来解决结果为 NULL 的情况
IFNULL 函数语法:
- IFNULL 函数接受两个参数,如果第一个参数不是 null 会直接返回,如果第一个参数是 null ,则返回第二个参数
- IFNULL(expression, alt_value)
expression
:需要检查是否为null的字段名或者表达式。alt_value
:如果expression
的值是null,则返回的备用值。简单说就是:expression为null时取alt_value
COALESCE 函数语法:
- COALESCE 函数可以接受两个或多个参数,并返回第一个非 null 的参数,如果所有参数都为 null,则会返回 null
COALESCE ( f1, f2, f3, ... fn)
COALESCE 用于:f1有值,返回f1,若为null,则看f2,f2有值,则返回f2,若为null,继续往后。以此类推。
简单说就是:f1若为null,则取f2,f2若为null,则取f3,以此类推。
2.SUM()可以求总记录数
1.SUM(1)相当于COUNT(1)
count(1):统计所有的记录(包括null)。当使用sum(1)时与count(*)相同,都是返回当前条件的记录数,但count效率会高点。
2.求符合条件的记录数
3.使用如果表达式:IF(expr, v1, v2)
expr 成立,返回结果 v1;否则,返回结果 v2
sum(true) 等同于 sum(1)
分数大于或等于 5 的返回 2
分数大于或等于 9 的返回 true
3.使用条件表达式
CASE WHEN 条件1 THEN 选项1 WHEN 条件2 THEN 选项2 …… ELSE 默认值 END
当score大于等于5时取1否则取0
当score大于等于9时取1否则取0