1.主要是使用case when 的写法需要将所有情况都考虑进来,但是有些情况下你不知道所有情况有哪些,即情况是不定的。
2.先来sql结果
SET @EE='';
SELECT
@EE:=CONCAT(@EE,'SUM(IF(type_string=\'',type_string,'\'',',amount_int,0)) AS ',type_string,',')
FROM
(SELECT DISTINCT type_string FROM test) A;
SET @QQ=CONCAT('SELECT left(test.time_date,7) AS month,',LEFT(@EE,LENGTH(@EE)-1),' FROM mytest GROUP BY left(test.time_date,7)');
PREPARE stmt FROM @QQ;
execute stmt;
deallocate prepare stmt;
注意,我将test_time做了截取的,test_time 是例如2020-01-01的格式,我的需求是统计每个月的数据,所以截取到了例如2020-01的字符。
注意替换,上面的type_string是名称,test_time是日期 ,amount_int是数值
3.需求
按照月份统计每个type的数据总和,结果集中第一列是时间(yyyy-mm),从第二列开始是每个名称对应的数量之和。
如下图
4.思路
一般的想法是,按照名称和日期分组计算,如下
select left(time_date,7) , type_string , sum(amount_int) from test GROUP BY left(time_date,7) , type_string
不过这样写的效果是这样
或者使用case when 的写法
select
left(test.time_date,7) as month,
sum(case when type_string='card' then amount_int else 0 end) as card,
sum(case when type_string='cash' then amount_int else 0 end) as cash,
sum(case when type_string='cheque' then amount_int else 0 end) as cheque,
sum(case when type_string='zhifubao' then amount_int else 0 end) as zhifubao
from
test
GROUP BY
left(test.time_date,7)
结果如下
这里就是需要将所有的type_string都使用case when写出来,假如不知道type_string将来会有多少种类呢?即如果type_string将来会增加呢?这样写就有局限性了。
5.附上建表语句
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
`emp_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '待补充' COMMENT '员工name',
`emp_cost` int(10) NULL DEFAULT NULL COMMENT '消费',
`score_int` int(10) NULL DEFAULT NULL,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`time_date` date NULL DEFAULT NULL,
`time1_string` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`type_string` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`amount_int` int(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test` VALUES (1, 'joe', 10, 10, 'a', '2021-04-23', '96.02', 'card', 10);
INSERT INTO `test` VALUES (2, 'sam', 20, 20, 'a', '2021-04-09', '110.25', 'card', 10);
INSERT INTO `test` VALUES (3, '待补充', NULL, 10, 'a', '2021-04-14', '80.00', 'cash', 20);
INSERT INTO `test` VALUES (4, '待补充', NULL, 5, 'a', '2021-01-01', '120.36', 'cheque', 20);
INSERT INTO `test` VALUES (5, '待补充', NULL, 6, 'a', '2021-02-01', '75.00', 'cash', 10);
INSERT INTO `test` VALUES (6, '待补充', NULL, 7, 'a', '2021-03-01', '71.25', 'card', 20);
INSERT INTO `test` VALUES (7, '待补充', NULL, 9, 'b', '2021-01-01', '85.63', 'card', 10);
INSERT INTO `test` VALUES (8, '待补充', NULL, 20, 'b', '2021-02-01', '86', 'cash', 20);
INSERT INTO `test` VALUES (9, '待补充', NULL, 15, 'b', '2021-03-01', '86.25', 'cash', 10);
INSERT INTO `test` VALUES (10, '待补充', NULL, 16, 'b', '2021-04-01', '99.99', 'cash', 20);
INSERT INTO `test` VALUES (11, '待补充', NULL, NULL, NULL, '2021-01-01', '', 'zhifubao', 12);