mysql 统计12月份数据，不足补0

最新推荐文章于 2024-03-11 16:22:17 发布

思远：

最新推荐文章于 2024-03-11 16:22:17 发布

阅读量1.5k

点赞数 1

分类专栏： MySql 文章标签：统计

本文链接：https://blog.csdn.net/qq_38080370/article/details/103420085

版权

MySql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

SELECT a.month, IFNULL(b.onLineTotal, 0) AS onLineTotal, IFNULL(b.offLineTotal, 0) AS offLineTotal
        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(t.create_time, '%m') AS MONTH,
                    COUNT(CASE WHEN online = 1 THEN online END) AS onLineTotal,
                    COUNT(CASE WHEN online = 0 THEN online END) AS offLineTotal
                FROM device t
                WHERE is_delete = 0
                    AND DATE_FORMAT(create_time, '%Y-%m') > DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m')
                GROUP BY MONTH
            ) b
            ON a.month = b.month
        ORDER BY a.month ASC

获取两个日期内的所有日期

SELECT * FROM (SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date FROM
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 
 7 UNION SELECT 8 UNION SELECT 9) t0,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 
 7 UNION SELECT 8 UNION SELECT 9) t1,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 
 7 UNION SELECT 8 UNION SELECT 9) t2,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 
 7 UNION SELECT 8 UNION SELECT 9) t3,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 
 7 UNION SELECT 8 UNION SELECT 9) t4) v WHERE selected_date BETWEEN '2012-02-10' AND '2012-02-15'