这是一个测试用的表 表名为users 查询总数量
Select count(*) from users查询近30天的数据量包括今天
SELECT COUNT(*) FROM users WHERE DATEDIFF(birthday,NOW())<=0 AND DATEDIFF(birthday,NOW())>-30 `` 查询上一周的数据SELECT a.click_date,IFNULL(b.count,0) AS COUNT FROM ( SELECT DATE_SUB(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-1), INTERVAL 1 DAY) AS click_date UNION ALL SELECT DATE_SUB(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-1), INTERVAL 2 DAY) AS click_date UNION ALL SELECT DATE_SUB(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-1), INTERVAL 3 DAY) AS click_date UNION ALL SELECT DATE_SUB(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-1), INTERVAL 4 DAY) AS click_date UNION ALL SELECT DATE_SUB(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-1), INTERVAL 5 DAY) AS click_date UNION ALL SELECT DATE_SUB(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-1), INTERVAL 6 DAY) AS click_date UNION ALL SELECT DATE_SUB(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-1), INTERVAL 7 DAY) AS click_date ) a LEFT JOIN ( SELECT DATE(时间字段) AS DATETIME, COUNT(*) AS COUNT FROM 表名 可以在这里加where条件 GROUP BY DATE(时间字段) ) b ON a.click_date = b.datetime ;
上周 SELECT * FROM users WHERE birthday>=DATE_ADD(NOW(),INTERVAL -(8 + WEEKDAY(NOW())) DAY) AND birthday<=DATE_ADD(NOW(),INTERVAL -(1 + WEEKDAY(NOW())) DAY);