报表sql语句汇总

背景：报表统计中，经常会涉及到各段时间的报表汇总，当天，昨天，近一周，一月，一季度，一年等，这里便整理了一些sql ,方便以后copy！

# 查询当天（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())