mysql的一些按日期和按24小时统计语句

1.统计当前日期前一周内，三个不同状态status的数据条数及总条数，且要把日期展示出来：

select
FROM_UNIXTIME(createdTime/1000, '%Y-%m-%d') as weekDay,
COUNT(case WHEN `status`=0 then `status` END) as status0Count,
COUNT(case WHEN `status`=1 then `status` END) as status1Count,
COUNT(case WHEN `status`=2 then `status` END) as status2Count,
count(id) as totalCount
from currTable
where 1=1
and FROM_UNIXTIME(createdTime/1000, '%Y-%m-%d') >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
GROUP BY FROM_UNIXTIME(createdTime/1000, '%Y-%m-%d') 
ORDER BY weekDay

统计结果：

PS:字段createdTime是时间戳数字，精确到毫秒，所以计算时需要除以1000到秒。如果有日期一条数据也没有，是不会显示的，除非使用一个虚拟时间表进行left join的操作，保证七个日期都展示，没有数据的全部为0.

2.统计某天24小时的分时数据量：

SELECT FROM_UNIXTIME(p.createdTime/1000, '%H') as curr_hour,
       count(p.id) as count 
FROM currTable p 
WHERE p.status=0 
 and (DAY(FROM_UNIXTIME(p.createdTime/1000, '%Y-%m-%d')) = DAY('2019-09-09')) 
GROUP BY FROM_UNIXTIME(p.createdTime/1000, '%H') ORDER BY curr_hour;

统计结果：