mysql 列转行和行转列

-- ----------------------------
-- Table structure for ol_test
-- ----------------------------
DROP TABLE IF EXISTS `ol_test`;
CREATE TABLE `ol_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` date DEFAULT NULL,
  `scount` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of ol_test
-- ----------------------------
INSERT INTO `ol_test` VALUES ('1', '小说', '2018-08-17', '10000');
INSERT INTO `ol_test` VALUES ('2', '小说', '2018-08-17', '15000');
INSERT INTO `ol_test` VALUES ('3', '微信', '2018-08-17', '20000');
INSERT INTO `ol_test` VALUES ('4', '小说', '2018-08-16', '35000');
INSERT INTO `ol_test` VALUES ('5', '微信', '2018-08-16', '36000');
INSERT INTO `ol_test` VALUES ('6', '小说', '2018-08-17', '30000');
INSERT INTO `ol_test` VALUES ('7', '微信', '2018-08-17', '35000');
INSERT INTO `ol_test` VALUES ('8', '小说', '2018-08-15', '35000');
INSERT INTO `ol_test` VALUES ('9', '微信', '2018-08-15', '38000');
INSERT INTO `ol_test` VALUES ('10', '小说', '2018-08-15', '37000');

-- ------------------------

-- 查看数据

-- ------------------------

select * from ol_test;

-- ------------------------ -- 列转行统计数据： max .. group by -- ------------------------

select 
    a.create_time as date,
    max(case a.name when '小说' then a.scount else 0 end) '小说',
    max(case a.name when '微信' then a.scount else 0 end) '微信' 
from ol_test a  
group by a.create_time；

 

-- ------------------------ -- 行转列统计数据 group by  -- ------------------------

select 
    a.create_time as date,
    a.name, 
    group_concat(a.name,'总量:',a.scount) as scount 
from  ol_test a
group by a.create_time,a.name;