-- ---------------------------- -- Table structure for `t_test` -- ---------------------------- DROP TABLE IF EXISTS `t_test`; CREATE TABLE `t_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `count` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
-- ---------------------------- -- Records of t_test -- ---------------------------- INSERT INTO `t_test` VALUES ('1', '微信', '2019-01-01 00:01:00', '100'); INSERT INTO `t_test` VALUES ('2', 'QQ', '2019-01-02 00:01:00', '100'); INSERT INTO `t_test` VALUES ('3', '微信', '2019-01-02 00:01:00', '200'); INSERT INTO `t_test` VALUES ('4', 'QQ', '2019-01-02 00:02:00', '200'); INSERT INTO `t_test` VALUES ('5', '微信', '2019-01-02 00:02:00', '300'); INSERT INTO `t_test` VALUES ('6', 'QQ', '2019-01-03 00:00:00', '300'); INSERT INTO `t_test` VALUES ('7', '微信', '2019-01-03 00:00:00', '400'); INSERT INTO `t_test` VALUES ('8', 'QQ', '2019-01-05 00:00:00', '400');
-- ------------------------
-- 行转列统计数据
-- ------------------------
SELECT DATE_FORMAT(create_time,'%Y-%m-%d') days, SUM(CASE name WHEN 'QQ' THEN count ELSE 0 END ) QQ, SUM(CASE name WHEN '微信' THEN count ELSE 0 END ) 微信 FROM t_test GROUP BY days
-- ------------------------
-- 列转行统计数据
-- ------------------------
SELECT days,GROUP_CONCAT(name,sumcount) FROM( SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as days,name, SUM(count) as sumcount FROM t_test GROUP BY days,name ) test GROUP BY days
SELECT days,GROUP_CONCAT(name,sumcount) FROM( SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as days,name, SUM(count) as sumcount FROM t_test GROUP BY days,name ) test GROUP BY days,name
