mysql按小时按天按时间统计数据

1、mysql按天进行统计
SELECT DATE_FORMAT(`字段名`, '%Y-%m-%d') AS days,COUNT(1) AS num FROM yizuotu_net
GROUP BY days;

%Y-%m-%d格式一定要和数据库时间格式对应

2、mysql按小时统计
SELECT DATE_FORMAT(`字段名`, '%Y-%m-%d %H') AS days,COUNT(1) AS num FROM yizuotu_net
GROUP BY days;

3、mysql按分统计
SELECT DATE_FORMAT(`字段名`, '%Y-%m-%d %H:%i') AS days,COUNT(1) AS num FROM yizuotu_net
GROUP BY days;

4、mysql按秒统计
SELECT DATE_FORMAT(`字段名`, '%Y-%m-%d %H:%i:%S') AS days,COUNT(1) AS num FROM yizuotu_net
GROUP BY days;

5、mysql按天查询数据
select * from yizuotu_net where  DATE_FORMAT(ptime,'%Y-%m-%d') = '2024-07-28'

6、mysql统计本周数据及对应个数
SELECT DATE_FORMAT(createtime, '%Y-%m-%d') dateTime,
count(1) countNumber
FROM yizuotu_net
WHERE YEARWEEK(date_format(createtime, '%Y-%m-%d')) = YEARWEEK(now())
GROUP BY dateTime;

7、mysql统计本周数据及对应个数，没有的自动补0
select
       d.date, IFNULL(T.countNumber, 0) countNumber
from (
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) + 0 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 1 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 2 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 3 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 4 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 5 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 6 DAY) as date
     ) d
         left join(
    select
          DATE_FORMAT(createtime, '%Y-%m-%d') dateTime,
               COUNT(1)                            countNumber
    from aa
    group by DATE_FORMAT(createtime, '%Y-%m-%d')
) T on T.dateTime = d.date
GROUP BY d.date;
 
8、mysql统计当月的数据
select DATE_FORMAT(createtime, '%Y-%m-%d') dateTime,
count(1) countNumber
    from yizuotu_net
where
    DATE_FORMAT(createtime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
GROUP BY dateTime;
 
9、mysql统计当月数据，不全的补0
select aa.DAY dateTime,
       (select count(*)
        from yizuotu_net t
        where substr(t.createtime, 1, 10) = aa.DAY and t.is_deleted = 0) as countNumber
from (SELECT date_add(DATE_ADD(curdate(), INTERVAL - DAY(curdate()) + 2 DAY),
                      INTERVAL (cast(help_topic_id AS signed INTEGER) - 1) DAY
                 ) DAY
      FROM zzzyk_com
      WHERE help_topic_id < DAY(last_day(curdate()))
      ORDER BY help_topic_id) aa;