这篇博客展示了如何使用MySQL进行时间周期的数据统计，包括按季度、周、月、天和年对数据进行计数。通过示例查询，演示了COUNT函数结合DATE、WEEK、MONTH、QUARTER和YEAR函数对`create_time`字段进行分组统计的方法。
/*查询2小时前的数据*/select * from tableName WHERE
create_time < DATE_SUB(NOW(), INTERVAL 2 HOUR)
SELECT
count(id)
FROM
rd_track_info
WHERE
DATE(create_time) < DATE_SUB(CURDATE(), INTERVAL 7 DAY);
SELECT
count(id)
FROM
rd_track_info
WHERE
DATE(create_time) = DATE_SUB(CURDATE(), INTERVAL 7 DAY);
SELECT
count(id)
FROM
rd_track_info
WHERE
DATE(create_time) > DATE_SUB(CURDATE(), INTERVAL 7 DAY);
/*按天统计*/
SELECT
count(id) countNum,
DATE(create_time) createTime
FROM
rd_track_info
GROUP BY
DATE(create_time)
ORDER BY
DATE(create_time) DESC;
/*按周统计*/
SELECT
count(id) countNum,
WEEK(create_time) createTime
FROM
rd_track_info
GROUP BY
WEEK(create_time)
ORDER BY
WEEK(create_time) DESC;
/*按月统计*/
SELECT
count(id) countNum,
MONTH(create_time) createTime
FROM
rd_track_info
GROUP BY
MONTH(create_time)
ORDER BY
MONTH(create_time) DESC;
/*按季度统计*/
SELECT
count(id) countNum,
QUARTER(create_time) createTime
FROM
rd_track_info
GROUP BY
QUARTER(create_time)
ORDER BY
QUARTER(create_time) DESC;
/*按年统计*/
SELECT
count(id) countNum,
YEAR(create_time) createTime
FROM
rd_track_info
GROUP BY
YEAR(create_time)
ORDER BY
YEAR(create_time) DESC;
原文：https://www.cnblogs.com/qcyye/p/13388181.html
423
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
