MySQL按天分组统计一定时间内的数据实例(没有数据补0)

SELECT DATE(CREATE_DATE) as date, COUNT(1) as count
FROM 表
WHERE 字段 = '1'
  AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d') >=
      DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d')
GROUP BY DATE(CREATE_DATE);

SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,data.num AS count
FROM (
         SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
         FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
               FROM 表
              ) day
         WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
               DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
         ORDER BY date
     ) dates
         LEFT JOIN (
    SELECT  COUNT(1) AS num, DATE(CREATE_DATE) AS time
    FROM audit_work_sheet
    WHERE 字段 = '1'
      AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >=
          DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
    GROUP BY DATE(CREATE_DATE)
) data ON DATE(time) = date
ORDER BY date;

SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,IFNULL(data.num, 0) AS count
FROM (
         SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
         FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
               FROM 表
              ) day
         WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
               DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
         ORDER BY date
     ) dates
         LEFT JOIN (
    SELECT  COUNT(1) AS num, DATE(CREATE_DATE) AS time
    FROM audit_work_sheet
    WHERE 字段 = '1'
      AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >=
          DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
    GROUP BY DATE(CREATE_DATE)
) data ON DATE(time) = date
ORDER BY date;

SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,data.level AS level, IFNULL(data.num, 0) AS count
FROM (
         SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
         FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
               FROM 表
              ) day
         WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
               DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
         ORDER BY date
     ) dates
         LEFT JOIN (
    SELECT RISK_LEVEL AS level, COUNT(1) AS num, DATE(CREATE_DATE) AS time
    FROM audit_work_sheet
    WHERE 字段 = '1'
      AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >=
          DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
    GROUP BY DATE(CREATE_DATE),level
) data ON DATE(time) = date
ORDER BY date,level;

select DATE_FORMAT(start_time,'%Y%m%d') days,count(product_no) count from test group by days;

select DATE_FORMAT(start_time,'%Y%u') weeks,count(product_no) count from test group by weeks;

select DATE_FORMAT(start_time,'%Y%m') months,count(product_no) count from test group bymonths;