1. 在开发时会遇到根据某个字段自定义规则分组,以下简单展示使用 mysql case when 的使用
准备测试数据:
DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score` (
`date` datetime NOT NULL,
`name` varchar(30) NOT NULL,
`bind_sum` varchar(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`date`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_score
-- ----------------------------
INSERT INTO `t_score` VALUES ('2018-12-01 00:00:00', '01', '10');
INSERT INTO `t_score` VALUES ('2018-12-01 00:00:00', '02', '20');
INSERT INTO `t_score` VALUES ('2018-12-01 00:00:00', '03', '30');
INSERT INTO `t_score` VALUES ('2018-12-02 00:00:00', '01', '20');
INSERT INTO `t_score` VALUES ('2018-12-02 00:00:00', '04', '30');
select * from t_score 的效果如下:
现在需要根据 date (时间) 和 name 自定义 01 (02 ,03),04 分组 求 bind_sum字段的和
select
date,
case when name = "01" then 1
when name in ("02","03") then 2
else 3 END as type,
sum(bind_sum) total
from t_score
GROUP BY date,type
结果如下 :