MySQl语句

这是一个测试用的表 表名为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);