博客

MySQL查每周、每月、每年、每天数据

每周
SELECT
name,
score,
join_time
FROM
culture_interest_xinfeng
WHERE  YEARWEEK(DATE_FORMAT(join_time,'%Y-%m-%d'),1) = YEARWEEK(NOW(),1)
order by score desc;

每月
SELECT
name,
score,
join_time
FROM
culture_interest_xinfeng
WHERE 
DATE_FORMAT(join_time,'%Y%m')= DATE_FORMAT(CURDATE(),'%Y%m')
order by score desc;

每年
SELECT
name,
score,
join_time
FROM
culture_interest_xinfeng
WHERE 
DATE_FORMAT(join_time,'%Y')= DATE_FORMAT(CURDATE(),'%Y')
order by score desc;

每天
SELECT
name,
score,
join_time
FROM
culture_interest_xinfeng
WHERE 
TO_DAYS(join_time) = TO_DAYS(NOW())
order by score desc;