MYSQL SQL查詢近7天一個月的數據的操作方法
MYSQL SQL查詢近7天,一個月的數據
//今天 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
select user_id, user_name, createtime from t_user where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(createtime);
同理,近一個星期為: INTERVAL 7 DAY。
參考網址:
https://www.cnblogs.com/zhangqunshi/p/6679847.html
https://www.jb51.net/article/133943.htm
補充:mysql 中sql 語句查詢今天、昨天、近7天、近30天、一個月內、上一月數據
1、幾個小時內的數據
DATE_SUB(NOW(), INTERVAL 5 HOUR)
2、今天
select * from 表名 where to_days(時間字段名) = to_days(now());
3、昨天
select * from 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 時間字段名) <= 1;
4、7天
select * from 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(時間字段名);
5、近30天
select * from 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(時間字段名);
6、本月
select * from 表名 WHERE DATE_FORMAT( 時間字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
7、上一月
select * from 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 時間字段名, '%Y%m' ) ) =1;
到此這篇關於MYSQL SQL查詢近7天,一個月的數據的文章就介紹到這瞭,更多相關mysql查詢近7天數據內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!