按时间分列求列数量（主要是数量的列会归零重置 ） ？

Question

原始表：
Id DevNo Count CreateTime
6566956 1 78 2019-6-21 0:00:00
6566957 2 99 2019-6-21 0:00:01
6566958 2 100 2019-6-21 0:00:02
6566959 3 30001 2019-6-21 0:00:02
6566960 3 30002 2019-6-21 0:00:03
6566961 1 80 2019-6-21 0:00:03
6566962 1 83 2019-6-21 0:00:04
6566963 2 102 2019-6-21 0:00:04
6566964 3 30005 2019-6-21 0:00:05
6566965 1 1 2019-6-21 0:01:00
6566966 1 5 2019-6-21 0:01:05

结果：
No 0:00:00至1:00:00 总数 2点 3点…23点
1 10 … …
2 3 … …
3 4 … …

3个采集器回来的数量，根据时间段每小时分列汇总数量，不能使用MAX-MIN因为中途数量会重置，例如1号采集器在01：00重置数量了

Answer 1

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 > '2017-09-02'
GROUP BY HOUR(e.createTime),e.devNo)d GROUP BY d.No