MySQL怎样按天分组统计一定时间内的数据

发布时间：2023-05-05 12:43:01 所属栏目：MySql教程来源：

导读：按天统计数据
1.没数据的一天过滤
SELECT DATE(CREATE_DATE) as date, COUNT(1) as count
FROM 表
WHERE 字段 = '1'
AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d') >=
DATE_FORMAT(DATE_

按天统计数据
1.没数据的一天过滤
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);
查询结果：

MySQL怎么按天分组统计一定时间内的数据

2.没数据的一天有日期总数为null
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;
查询结果：

MySQL怎么按天分组统计一定时间内的数据

3.没数据的一天有日期总数为0
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;
查询结果

MySQL怎么按天分组统计一定时间内的数据

4.加入其它分组字段没数据的一天有日期总数为0
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;

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!