mysql常用时间列表的查询——七天内、本月、本周、某天

 在mysql用分组查询做统计时,时常会遇到这样的场景——需要查询一段时间内数据统计,并按一定的时间分组查出。分组查询的sql可能很快就写出来了,但当执行后却发现,由于某个时间段没有数据,导致输出结果的时间段缺失。

 

如下图,本意是想查询当月各天的金额统计,但由于数据缺失,导致查询结果只有部分天数输出。这个输出单纯地对于后台来说可能是没有什么问题,但要前端将它展示成图表那可就不行了!

 

要想做到按时间段分组输出且不丢失数据,单纯只是对原表操作是不行的,最起码也需要通过SELECT语句来生出些临时表作连接,才能达到以上目的。除此之外,还需要额外创建个含有31行数据辅助表。在此,我偷了个小懒,直接地使用了mysql的help_topic表做为辅助表。以下将展示几种常见地的查询时间段列表的方法。

 最近七天的日期列表

SELECT @s :=@s + 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := -1) temp
WHERE @s < 6
ORDER BY `date`
 一天24小时查询(0 - 23)

SELECT @d := @d + 1 `hour`
FROM mysql.help_topic,(SELECT @d := -1) temp
WHERE @d < 23
ORDER BY `hour`
 本周日期列表

SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < 6 - WEEKDAY(CURRENT_DATE)
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := WEEKDAY(CURRENT_DATE) + 1) temp
WHERE @s > 0
ORDER BY `date`
本月日期列表

SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < DAY(LAST_DAY(CURRENT_DATE)) - DAY(CURRENT_DATE)
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := day(CURRENT_DATE)) temp
WHERE @s > 0
ORDER BY `date`
有了上面几张临时表,解决时间段缺失的问题就简单了。只需要按需选取上述临时表作为主表,连接统计sql即可。

SELECT day_temp.date, COALESCE(stat_price.price, 0) AS price
FROM (
SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < DAY(LAST_DAY(CURRENT_DATE)) - DAY(CURRENT_DATE)
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := day(CURRENT_DATE)) temp
WHERE @s > 0
ORDER BY `date`
) AS day_temp
LEFT JOIN
(
SELECT DATE(created_date) AS date, SUM(total_price) AS price
FROM t_order
WHERE MONTH(created_date) = MONTH(CURRENT_DATE)
AND YEAR(created_date) = YEAR(CURRENT_DATE)
GROUP BY DATE(created_date)
) AS stat_price
ON day_temp.date = stat_price.date
 其中,COALESCE函数的作用是返回第一个非空结果。sql执行结果如下:

 

结果图就不全截,有兴趣的话,大家也可以去试下。如果有其他方法或是更好的方法的话也欢迎留言! 
————————————————
版权声明:本文为CSDN博主「zy丶秋水」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_34902684/article/details/82989844

mysql常用时间列表的查询——七天内、本月、本周、某天

上一篇:freemodbus移植、实例及其测试方法


下一篇:Mysql索引及执行计划