MySQL中获取季度、年度等数据
上一篇讲了如何获取天、周、月等数据，本篇介绍一下如何获取季度、年度数据，以及一些特殊的日期需求
1、本季度
QUARTER(CURDATE())
示例：
select *
from tablename
where
QUARTER(time_ziduan)= QUARTER(CURDATE())
2、上季度
QUARTER(DATE_SUB(CURDATE()，INTERVAL 1 QUARTER))
示例：
select *
from tablename
where
QUARTER(time_ziduan)= QUARTER(DATE_SUB(CURDATE()，INTERVAL 1 QUARTER))
3、本年度
YEAR(CURDATE())
或
YEAR(NOW())
示例：
select *
from tablename
where
YEAR(time_ziduan)= YEAR(CURDATE())
4、上年度
YEAR(DATE_SUB(CURDATE(),INTERVAL 1 YEAR))
或
YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))
示例：
select *
from tablename
where
YEAR(time_ziduan)= YEAR(DATE_SUB(CURDATE(),INTERVAL 1 YEAR))
5、特殊日期需求
DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1DAY) 本月第一天
LAST_DAY(CURDATE()) 本月最后一天
DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1DAY),INTERVAL 1MONTH) 下月第一天
下面来一段示例：
SELECT
`渠道`
,SUM(case when YEAR(`应还款日期`)= YEAR(DATE_SUB(CURDATE(),INTERVAL 1 YEAR)) THEN `应还本金金额`+`应还利息金额` ELSE 0 END) as '上年度应还金额'
,SUM(case when YEAR(`应还款日期`)= YEAR(CURDATE()) THEN `应还本金金额`+`应还利息金额` ELSE 0 END) as '本年度应还金额'
,SUM(case when QUARTER(`应还款日期`)= QUARTER(DATE_SUB(CURDATE(),INTERVAL 1 QUARTER)) THEN `应还本金金额`+`应还利息金额` ELSE 0 END) as '上季度应还金额'
,SUM(case when QUARTER(`应还款日期`)= QUARTER(CURDATE()) THEN `应还本金金额`+`应还利息金额` ELSE 0 END) as '本季度应还金额'
FROM
dq_daichang_qingdan
GROUP BY
`渠道`
运行结果如下：