# 查询当天(1)
select * from invoice where cr_date like '2021-05-25%'
# 查询当天(2)
SELECT * FROM invoice WHERE TO_DAYS(cr_date) = to_days(now());
#查询当天所有金额总计
SELECT SUM(money) as '当天发票总额' from invoice WHERE TO_DAYS(cr_date) = to_days(now());
#查看昨天
SELECT * FROM invoice WHERE to_days(now())-to_days(`cr_date`) = 1;
#查看近七天
SELECT * FROM invoice where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(cr_date) ;
#查看近30天
SELECT * FROM invoice where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(cr_date) ;
#查看本月 # DATE_FORMAT( CURDATE() , '%Y%m' ) 年份月份去点- 如:202105
SELECT * FROM invoice WHERE DATE_FORMAT( cr_date, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' );
Select * from invoice where cr_date LIKE '2021-05%';
#查看本月(2)
select * from invoice where month(cr_date) =
month(curdate()) and year(cr_date) = year(curdate())
#查询上周 **周日为第一天,周六为第七天
Select * from invoice where YEARWEEK(date_format(`cr_date`,'%Y-%m-%d')) = YEARWEEK(now())-1 order by cr_date;
Select * from invoice where DATE_FORMAT(cr_date,'%U') = DATE_FORMAT(now(),'%U')-1 order by cr_date;
# 查询上周 周一为第一天
Select * from invoice where DATE_FORMAT(cr_date,'%u') = DATE_FORMAT(now(),'%u')-1 order by cr_date;
#查询一年中每一周
select DATE_FORMAT(cr_date,'%x年-第%v周') as week,sum(money) as money from invoice where DATE_FORMAT(cr_date,'%Y')=2021 group by
week
#查询上月 # date_format(DATE_SUB( CURDATE(), INTERVAL 1 MONTH ),'%Y %m'):2021 04
#DATE_SUB(date,INTERVAL expr unit) 从日期减去指定的时间间隔
SELECT * FROM invoice WHERE date_format( cr_date, '%Y %m' ) = date_format(DATE_SUB( CURDATE(), INTERVAL 1 MONTH ),'%Y %m')
#查询季度
#YEAR(cr_date)*10+((MONTH(cr_date)-1) DIV 3) +1 计算出来的是哪一年的第几季度,如20211
select YEAR(cr_date)*10+((MONTH(cr_date)-1) DIV 3) +1,sum(money) as summoney
from invoice group by YEAR(cr_date)*10+((MONTH(cr_date)-1) DIV 3) +1;
#查询指定时间段
Select sum(money) from invoice where cr_date >='2021-04-01' and cr_date <='2021-06-30'
#查询年度
select * from invoice where YEAR(cr_date) =
YEAR(curdate())