SQL | DB2和MySQL取近几个季度末和近几个月末时间

数据库取近几个季度末和月末时间，以用DB2和MySQL取近4个季度末和近12个月末为例。

取近4个季度末

DB2语句

WITH T (STARTDATE) AS
(
VALUES CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAY - (MOD(MONTH(CURRENT_DATE)-1,3)) MONTH
UNION ALL
SELECT STARTDATE - 3 MONTH
FROM T
WHERE CURRENT_DATE <= STARTDATE - 3 MONTH + 1 YEAR
)
SELECT STARTDATE - 1 DAY ENDDATE_DB2
FROM T
ORDER BY STARTDATE DESC

MySQL 语句

WITH RECURSIVE T (STARTDATE) AS 
(SELECTCURRENT_DATE() - INTERVAL (DAY(CURRENT_DATE()) - 1) DAY - INTERVAL (MOD(MONTH(CURRENT_DATE())-1,3)) MONTHUNION ALLSELECTSTARTDATE - INTERVAL 3 MONTHFROMTWHERE CURRENT_DATE() < STARTDATE - INTERVAL 3 MONTH + INTERVAL 1 YEAR)SELECTSTARTDATE - INTERVAL 1 DAY QUENDDATEFROM T
ORDER BY STARTDATE DESC

取近12个月末

 DB2语句

WITH T (STARTDATE) AS
(
VALUES CURRENT_DATE - (DAY(CURRENT_DATE) - 1) DAY
UNION ALL
SELECT STARTDATE - 1 MONTH
FROM T
WHERE CURRENT_DATE <= STARTDATE + 11 MONTH
)
SELECT STARTDATE - 1 DAY MONTHEND_DB2
FROM T
ORDER BY STARTDATE DESC

 MySQL语句

WITH RECURSIVE T (STARTDATE) AS 
(SELECTCURRENT_DATE() - INTERVAL (DAY(CURRENT_DATE()) - 1) DAYUNION ALLSELECTSTARTDATE - INTERVAL 1 MONTHFROM TWHERE CURRENT_DATE() < STARTDATE - INTERVAL 1 MONTH + INTERVAL 1 YEAR)SELECTSTARTDATE - INTERVAL 1 DAY MONTHEND_MYSQLFROM T
ORDER BY STARTDATE DESC

如有不足之处，还请各位大神指正~