mysql中怎么取季度数据_MySQL中获取季度、年度等数据

上一篇讲了如何获取天、周、月等数据，本篇介绍一下如何获取季度、年度数据，以及一些特殊的日期需求

1、本季度

QUARTER(CURDATE())

示例：

select *

from tablename

where

QUARTER(time_ziduan)= QUARTER(CURDATE())

2、上季度

QUARTER(DATE_SUB(CURDATE()，INTERVAL1QUARTER))

示例：

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(),INTERVAL1YEAR))

或

YEAR(DATE_SUB(NOW(),INTERVAL1YEAR))

示例：

select *

from tablename

where

YEAR(time_ziduan)=YEAR(DATE_SUB(CURDATE(),INTERVAL1YEAR))

5、特殊日期需求

DATE_ADD(CURDATE(),INTERVAL-DAY(CURDATE())+1DAY)本月第一天

LAST_DAY(CURDATE())本月最后一天

DATE_ADD(DATE_ADD(CURDATE(),INTERVAL-DAY(CURDATE())+1DAY),INTERVAL1MONTH)下月第一天

下面来一段示例：

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

`渠道`

运行结果如下：