Mysql数据统计sql(一)

Mysql数据统计sql(一)

1、查询当前时间的前2小时之前的数据

select * from person_base_info WHERE create_time < DATE_SUB(NOW(), INTERVAL 2 HOUR)

2、查询当前时间7天之前的数据、7天之前之内的数据，刚好7天的数据

SELECT count(uuid) AS cont FROM person_base_info WHERE DATE(create_time) < DATE_SUB(CURDATE(), INTERVAL 7 DAY);
SELECT count(uuid) AS cont FROM person_base_info WHERE DATE(create_time) > DATE_SUB(CURDATE(), INTERVAL 7 DAY);
SELECT count(uuid) AS cont FROM person_base_info WHERE DATE(create_time) = DATE_SUB(CURDATE(), INTERVAL 7 DAY);

3.按天统计
– 第一种方式
SELECT DATE(create_time) createTime,count(uuid) countNum FROM person_base_info
GROUP BY DATE(create_time) ORDER BY DATE(create_time) DESC;
– 第二种方式
SELECT DATE_FORMAT(create_time,’%Y-%m-%d’)createTime ,COUNT(uuid) countNum FROM person_base_info
GROUP BY DATE_FORMAT(create_time,’%Y-%m-%d’)

4.按月统计
SELECT DATE_FORMAT(create_time,’%Y-%m’)createTime ,COUNT(uuid)countNum
FROM person_base_info
GROUP BY DATE_FORMAT(create_time,’%Y-%m’)
– 另一种方式
SELECT MONTH(create_time) createTime,count(uuid) countNum FROM person_base_info
GROUP BY MONTH(create_time) ORDER BY MONTH(create_time) DESC;