MYSQL SQL查询近7天，一个月的数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
//今天select * from 表名 where to_days(时间字段名) = to_days(now());//昨天SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1//近7天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)//近30天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)//本月SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )//上一月SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1//查询本季度数据select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());//查询上季度数据select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));//查询本年数据select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());//查询上年数据select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));//查询当前这周的数据SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());//查询上周的数据SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;//查询上个月的数据select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) select * from user where pudate between 上月最后一天 and 下月第一天//查询当前月份的数据select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m') |
近一个月统计SQL
|
1
2
3
4
5
6
7
8
|
select user_id, user_name, createtimefrom t_userwhere DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(createtime); |
同理，近一个星期为: INTERVAL 7 DAY。
参考网址：
https://www.cnblogs.com/zhangqunshi/p/6679847.html
补充：mysql 中sql 语句查询今天、昨天、近7天、近30天、一个月内、上一月数据
1、几个小时内的数据
|
1
|
DATE_SUB(NOW(), INTERVAL 5 HOUR) |
2、今天
|
1
|
select * from 表名 where to_days(时间字段名) = to_days(now()); |
3、昨天
|
1
|
select * from 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1; |
4、7天
|
1
|
select * from 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名); |
5、近30天
|
1
|
select * from 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名); |
6、本月
|
1
|
select * from 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ); |
7、上一月
|
1
|
select * from 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1; |
到此这篇关于MYSQL SQL查询近7天，一个月的数据的文章就介绍到这了,更多相关mysql查询近7天数据内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://www.cnblogs.com/canfengfeixue/archive/2023/04/26/17355656.html
