MYSQL 获取最近指定日期的数据

Yurjn

已于 2022-08-23 19:07:32 修改

阅读量1.4k

点赞数

分类专栏： SQL 文章标签： mysql sql 数据库

于 2022-08-23 19:01:52 首次发布

本文链接：https://blog.csdn.net/xiaoyue2540/article/details/126491044

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

获取最近3天数据

SELECT a.weekday, COUNT(sl.id) count FROM ( SELECT DATE_SUB(CURDATE(), INTERVAL 0 DAY) AS date
UNION
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)
UNION
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) ) as a 
LEFT JOIN SpeakandChat_log as sl on FROM_UNIXTIME(sl.time/1000, '%Y-%m-%d')=a.weekday 
GROUP BY a.weekday;

Python 生成SQL语句

# 最近30天
sql = "SELECT a.date, COUNT(sl.id) count FROM ( SELECT DATE_SUB(CURDATE(), INTERVAL 0 DAY) AS date "
for i in range(29):
    sql += 'UNION SELECT DATE_SUB(CURDATE(), INTERVAL ' + str(i + 1) + ' DAY)'
sql += " ) as a LEFT JOIN SpeakandChat_log as sl on FROM_UNIXTIME(sl.time/1000, '%Y-%m-%d')=a.date GROUP BY a.date;"