mysql查询当天当周当月的数据
1、查询当天的数据select * from 表名 where TO_DAYS(时间字段)=TO_DAYS(NOW());2、查询当周的数据select * from 表名 where YEARWEEK(DATE_FORMAT(时间字段,'%Y-%m-%d'))=YEARWEEK(NOW());3、查询当月的数据select * from 表名 where DATE_FORMAT(时间字段,'%Y
·
1、查询当天的数据
select * from table_name where TO_DAYS(时间字段)=TO_DAYS(NOW());
2、查询当周的数据
select * from table_name where YEARWEEK(DATE_FORMAT(时间字段,'%Y-%m-%d'))=YEARWEEK(NOW());
3、查询当月的数据
select * from table_name where DATE_FORMAT(时间字段,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');
4、查询昨天的数据
select * from table_name where TO_DAYS(NOW())-TO_DAYS(时间字段)=1;
5、查询最近7天的数据
select * from table_name where DATE_SUB(CURDATE(),INTERVAL 7 DAY)<=DATE(时间字段);
6、查询当年的数据
select * from table_name where YEAR(时间字段) =YEAR(NOW());
7、查询上周的数据
select * from table_name whereYEARWEEK(DATE_FORMAT(时间字段,'%Y-%m-%d'))=YEARWEEK(NOW())-1;
8、查询上月的数据
select *from table_name
where PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(时间字段,'%Y%m');
9、查询本季度的数据
select * from table_name where QUARTER(时间字段)=QUARTER(now());
10、查询上季度的数据
select * from table_name where QUARTER(时间字段)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
11、查询本年的数据
select * from table_name where YEAR(时间字段)=YEAR(NOW());
12、查询上年的数据
select * from table_name where year(时间字段)=year(date_sub(now(),interval 1 year));
13、按照天统计数据
SELECT
count(id) countNum,
DATE(时间字段) createTime
FROM
baj_checkimgs
GROUP BY
DATE(时间字段)
ORDER BY
DATE(时间字段) DESC;
14、按照周统计数据
SELECT
count(id) countNum,
WEEK(时间字段) createTime
FROM
baj_checkimgs
GROUP BY
WEEK(时间字段)
ORDER BY
WEEK(时间字段) DESC;
15、按月统计
SELECT
count(id) countNum,
MONTH(时间字段) createTime
FROM
baj_checkimgs
GROUP BY
MONTH(时间字段)
ORDER BY
MONTH(时间字段) DESC;
16、按照季度统计
SELECT
count(id) countNum,
QUARTER(时间字段) createTime
FROM
baj_checkimgs
GROUP BY
QUARTER(时间字段)
ORDER BY
QUARTER(时间字段) DESC;
17、按照年统计
SELECT
count(id) countNum,
YEAR(时间字段) createTime
FROM
baj_checkimgs
GROUP BY
YEAR(时间字段)
ORDER BY
YEAR(时间字段) DESC;
更多推荐
已为社区贡献3条内容
所有评论(0)