sql 语句查询近七天，近十二个月的数据

tanfei_

已于 2023-03-23 22:20:37 修改

阅读量360

点赞数

文章标签： sql 数据库 mybatis

于 2023-03-14 20:33:18 首次发布

本文链接：https://blog.csdn.net/tanfei_/article/details/129539458

版权

该SQL查询分为两部分：第一部分计算过去七天每天的维修记录数量，第二部分列出近十二个月的每月维修记录。通过LEFTJOIN从sys_repair表中获取数据，使用DATE_FORMAT处理日期，并用INTERVAL间隔来获取不同日期范围。

摘要由CSDN通过智能技术生成

SELECT a.countKey now_time,a.countValue nums FROM
        (SELECT DATE_FORMAT(mycreatedate,'%m-%d') countKey,IFNULL(COUNT(r.`create_time`), 0) countValue  FROM (
        SELECT CURDATE() mycreatedate
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) mycreatedate
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) mycreatedate
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) mycreatedate
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) mycreatedate
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) mycreatedate
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) mycreatedate
        UNION ALL
        SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) mycreatedate
        ) t LEFT JOIN sys_repair r  ON DATE_FORMAT(r.`create_time`,'%Y-%m-%d')=t.mycreatedate
        GROUP BY  mycreatedate ) a

近十二个月

SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 12 MONTH), '%Y-%m') AS `month`