最近在做一些对于统计方面的开发，整顿了以下对于各个时间段的查问
查问当天内各时段的XX统计(比方小时客流量)
阐明
表名：cg_event_tendency_num_hour_statistics
数据库天字段：event_date
数据库工夫字段：event_hour ==> 返回给前端as为name
数据库以后时间段数量字段：event_num ==> 返回给前端as为value
SELECT event_hour,SUM( event_num ) as event_num FROM cg_event_tendency_num_hour_statistics WHERE date_format(event_date, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d') GROUP BY event_hour这里会呈现一个问题，数据查问进去的可能不是残缺的时间段
然而咱们想返回给前端残缺的工夫00-23该怎么操作呢？
咱们能够应用LEFT JOIN来进行操作，先应用 UNION ALL查问出00-23的全副工夫,再将实在数据进行左关联
残缺sql如下：
SELECT timetable.HOUR AS NAME, ifnull(sumtable.count, 0) AS VALUEFROM ( SELECT 0 AS HOUR UNION ALL SELECT 1 AS HOUR UNION ALL SELECT 2 AS HOUR UNION ALL SELECT 3 AS HOUR UNION ALL SELECT 4 AS HOUR UNION ALL SELECT 5 AS HOUR UNION ALL SELECT 6 AS HOUR UNION ALL SELECT 7 AS HOUR UNION ALL SELECT 8 AS HOUR UNION ALL SELECT 9 AS HOUR UNION ALL SELECT 10 AS HOUR UNION ALL SELECT 11 AS HOUR UNION ALL SELECT 12 AS HOUR UNION ALL SELECT 13 AS HOUR UNION ALL SELECT 14 AS HOUR UNION ALL SELECT 15 AS HOUR UNION ALL SELECT 16 AS HOUR UNION ALL SELECT 17 AS HOUR UNION ALL SELECT 18 AS HOUR UNION ALL SELECT 19 AS HOUR UNION ALL SELECT 20 AS HOUR UNION ALL SELECT 21 AS HOUR UNION ALL SELECT 22 AS HOUR UNION ALL SELECT 23 AS HOUR) timetable LEFT JOIN ( SELECT event_hour AS HOUR, SUM(event_num) AS count FROM cg_event_tendency_num_hour_statistics WHERE date_format(event_date, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d') GROUP BY HOUR ) sumtable ON timetable.HOUR = sumtable.HOURORDER BY NAME后果：
查问当月内每天的XX统计(比方日客流量)
阐明
表名：cg_event_tendency_num_statistics
数据库月字段：event_month
数据库天字段：event_date ==> 返回给前端as为name
数据库以后时间段数量字段：event_num ==> 返回给前端as为value
SELECT event_date , SUM( event_num ) as event_num FROM cg_event_tendency_num_statistics WHERE event_month = DATE_FORMAT( now(), '%Y-%m' ) GROUP BY event_date这里也是同样的问题，数据查问进去的可能不是残缺的日期段
解决形式和小时的同理,只是这里采纳办法生成了每月的天数
SELECT DAY(timetable.date) AS NAME, ifnull(sumtable.count, 0) AS VALUEFROM ( SELECT date, count FROM ( SELECT DATE_SUB(last_day(curdate()), INTERVAL xc - 1 DAY) AS date, 0 AS count FROM ( SELECT @xi := @xi + 1 AS xc FROM ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) xc1, ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) xc2, ( SELECT @xi := 0 ) xc0 ) xcxc ) x0 WHERE x0.date >= ( SELECT date_add(curdate(), INTERVAL -DAY(curdate()) + 1 DAY) ) ORDER BY date) timetable LEFT JOIN ( SELECT event_date AS date, SUM(event_num) AS count FROM cg_event_tendency_num_statistics WHERE event_month = DATE_FORMAT(now(), '%Y-%m') GROUP BY date ) sumtable ON timetable.date = sumtable.dateORDER BY NAME后果：
年客流量的形式和工夫的差不多，这里就不整顿了其余的后续再进行更新