Mysql 统计当年每个月的数据当月没有自动补0

最新推荐文章于 2023-08-02 10:40:02 发布

林云记忆

最新推荐文章于 2023-08-02 10:40:02 发布

阅读量566

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_47721612/article/details/129712338

版权

SELECT a.month, IFNULL(b.max, 0) AS max, IFNULL(b.min, 0) AS min
        FROM (
            SELECT DATE_FORMAT(CURDATE(), '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 2 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 3 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 4 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 5 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 6 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 7 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 8 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 9 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 10 MONTH, '%m') AS MONTH
            UNION
            SELECT DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, '%m') AS MONTH
        ) a
            LEFT JOIN (
                SELECT DATE_FORMAT(created_at, '%m') AS MONTH,
                   MAX(temperature) max,
									 MIN(temperature) min
                 FROM pressure_statistics 
                WHERE
                     DATE_FORMAT(created_at, '%Y-%m') > DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m')
										 and  YEAR(created_at)="2023"
										 and organize_id=0
                GROUP BY MONTH
            ) b
            ON a.month = b.month
        ORDER BY a.month ASC