查询当天24H内的每小时数据和当天最早、当天最新的数据

最近遇到个需求，查询当天内24小时内的每个小时总数，找了一圈，发现这种写法挺符合要求，虽然有点麻烦，下面是SQL是

SELECT
DATE_FORMAT(s.scanTime, '%Y%m%d') as '日期',
count(s.scanNo) as '一天的总量',
count(HOUR(s.scanTime)='0' or null ) as '0点',
count(HOUR(s.scanTime)='1' or null ) as '1点',
count(HOUR(s.scanTime)='2' or null ) as '2点',
count(HOUR(s.scanTime)='3' or null ) as '3点',
count(HOUR(s.scanTime)='4' or null ) as '4点',
count(HOUR(s.scanTime)='5' or null ) as '5点',
count(HOUR(s.scanTime)='6' or null ) as '6点',
count(HOUR(s.scanTime)='7' or null ) as '7点',
count(HOUR(s.scanTime)='8' or null ) as '8点',
count(HOUR(s.scanTime)='9' or null ) as '9点',
count(HOUR(s.scanTime)='10' or null ) as '10点',
count(HOUR(s.scanTime)='11' or null ) as '11点',
count(HOUR(s.scanTime)='12' or null ) as '12点',
count(HOUR(s.scanTime)='13' or null ) as '13点',
count(HOUR(s.scanTime)='14' or null ) as '14点',
count(HOUR(s.scanTime)='15' or null ) as '15点',
count(HOUR(s.scanTime)='16' or null ) as '16点',
count(HOUR(s.scanTime)='17' or null ) as '17点',
count(HOUR(s.scanTime)='18' or null ) as '18点',
count(HOUR(s.scanTime)='19' or null ) as '19点',
count(HOUR(s.scanTime)='20' or null ) as '20点',
count(HOUR(s.scanTime)='21' or null ) as '21点',
count(HOUR(s.scanTime)='22' or null ) as '22点',
count(HOUR(s.scanTime)='23' or null ) as '23点'
FROM
barcode s
where DATE_FORMAT(s.scanTime, '%Y%m%d') = CURDATE() and s.ulocNo='T0'
GROUP BY
DATE_FORMAT(s.scanTime, '%Y%m%d')

运行看一下效果

 一些小思路：

如何查询当天的第一条数据

SELECT s.scanNo vin FROM barcode s
where s.ulocNo='T0' and DATEDIFF(s.scanTime,NOW()) = 0 LIMIT 1

如何查询当天最新一条数据

select scanNo as vin from barcode s 
where s.ulocNo='T0'  ORDER BY s.scanTime desc limit 0,1

按时间排序的当天所有数据

SELECT s.scanNo as VIN,s.scanTime FROM `barcode` s 
where s.ulocNo='GA11T2001' and date(s.scanTime)=CURDATE()

有个别情况，用当天函数查数据时，会查到非今天的数据。这种情况一般都是数据库在安装时，时区时间戳没设置对；

show variables like "%time_zone%";

有修改时区的链接，mysql 时间转指定时区时间戳_小47丫的博客-CSDN博客_mysql转换时区