统计一组数据中的每月有几条

SELECT  DATE_FORMAT(create_time, '%m') AS month, COUNT(*) AS sum
FROM 
GROUP BY month;

表

结果

统计十二个月每月有几条的数据

思路

• 第一步拿到近十二个月时间
• 第二步查询每月数据条数
• 组合查询语句 得到最终结果

sql

• 第一步拿到近十二个月时间
• interval 1 day ，解释为将时间间隔设置为1天。
• SELECT DATE_FORMAT(CURDATE(), '%Y.%m') AS month; 当前时间月
• SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y.%m') AS month；上一个月时间

      

        SELECT DATE_FORMAT(CURDATE(), '%Y.%m') AS `month1`
              UNION
              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`

• 第二步查询每月数据条数

  SELECT DATE_FORMAT(create_time, '%Y.%m') AS month2, COUNT(*) AS monthNum
                            FROM u_user  u
                           where 1= 1
                            GROUP BY month2

• 组合查询语句 得到最终结果

select a.month1 month, b.monthNum
        from (SELECT DATE_FORMAT(CURDATE(), '%Y.%m') AS `month1`
              UNION
              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`) a
                 left join (SELECT DATE_FORMAT(create_time, '%Y.%m') AS month2, COUNT(*) AS monthNum
                            FROM u_user  u
                            where 1=1
                            GROUP BY month2) b on a.month1 = b.month2
        ORDER BY a.month1