sql行转列，按小时记录

表

CREATE TABLE `demo`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `devNo` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `count` int(11) NULL DEFAULT NULL,
  `createTime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

 

select d.No,
SUM(CASE Hour WHEN '01' THEN d.Count ELSE 0 END) as '01',
SUM(CASE Hour WHEN '02' THEN d.Count ELSE 0 END) as '02',
SUM(CASE Hour WHEN '03' THEN d.Count ELSE 0 END) as '03',
SUM(CASE Hour WHEN '04' THEN d.Count ELSE 0 END) as '04' ,
SUM(CASE Hour WHEN '05' THEN d.Count ELSE 0 END) as '05' ,
SUM(CASE Hour WHEN '06' THEN d.Count ELSE 0 END) as '06' ,
SUM(CASE Hour WHEN '07' THEN d.Count ELSE 0 END) as '07' ,
SUM(CASE Hour WHEN '08' THEN d.Count ELSE 0 END) as '08' ,
SUM(CASE Hour WHEN '09' THEN d.Count ELSE 0 END) as '09' ,
SUM(CASE Hour WHEN '10' THEN d.Count ELSE 0 END) as '10' ,
SUM(CASE Hour WHEN '11' THEN d.Count ELSE 0 END) as '11' ,
SUM(CASE Hour WHEN '12' THEN d.Count ELSE 0 END) as '12' ,
SUM(CASE Hour WHEN '13' THEN d.Count ELSE 0 END) as '13' ,
SUM(CASE Hour WHEN '14' THEN d.Count ELSE 0 END) as '14' ,
SUM(CASE Hour WHEN '15' THEN d.Count ELSE 0 END) as '15' ,
SUM(CASE Hour WHEN '16' THEN d.Count ELSE 0 END) as '16' ,
SUM(CASE Hour WHEN '17' THEN d.Count ELSE 0 END) as '17' ,
SUM(CASE Hour WHEN '18' THEN d.Count ELSE 0 END) as '18' ,
SUM(CASE Hour WHEN '19' THEN d.Count ELSE 0 END) as '19' ,
SUM(CASE Hour WHEN '20' THEN d.Count ELSE 0 END) as '20' ,
SUM(CASE Hour WHEN '21' THEN d.Count ELSE 0 END) as '21' ,
SUM(CASE Hour WHEN '22' THEN d.Count ELSE 0 END) as '22' ,
SUM(CASE Hour WHEN '23' THEN d.Count ELSE 0 END) as '23' ,
SUM(CASE Hour WHEN '24' THEN d.Count ELSE 0 END) as '24'

from
(SELECT e.devNo as No, HOUR(e.createTime) as Hour,sum(e.count) as Count 
FROM demo e 
WHERE e.createTime > '2019-06-28' 
GROUP BY HOUR(e.createTime),e.devNo)d GROUP BY d.No