网上找了很久才找到的一个解决方案,将这个方案转载出来供大家参考,下面以sum函数为例
建表、数据
CREATE TABLE `student` (
`name` varchar(10) DEFAULT NULL COMMENT '姓名',
`sex` varchar(1) DEFAULT NULL COMMENT '性别',
`id` varchar(2) DEFAULT NULL COMMENT '主键'
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `eye`.`student` (`name`, `sex`, `age`) VALUES ('顾二嫂', '女', '30');
INSERT INTO `eye`.`student` (`name`, `sex`, `age`) VALUES ('宋江', '男', '48');
INSERT INTO `eye`.`student` (`name`, `sex`, `age`) VALUES ('林冲', '男', '35');
INSERT INTO `eye`.`student` (`name`, `sex`, `age`) VALUES ('娇娘', '女', NULL);
mysql默认的sum函数处理结果
SELECT
sex AS '性别',
sum( age ) AS '总年龄'
FROM
student
GROUP BY
sex
以下是没有忽略null的处理方案(对于值为null的情况,sum函数会把当前值当0处理,那么我们只要让为null的值大于0即可)
SELECT
sex AS '性别',
( CASE WHEN sum( IFNULL( age, 1 )) > sum( age ) THEN NULL ELSE sum( age ) END ) AS '总年龄'
FROM
student
GROUP BY
sex
转载于:https://my.oschina.net/u/3536632/blog/3074268