问题:选择某月后,统计选择月之前的连续12个月的数据
思路:建立一张你年份月份表,
SELECT
MONTH AS name,
IFNULL(num, 0) AS
value
FROM
(
SELECT datelist,MONTH
FROM sys_calendar
WHERE
<if test="month != null and month != ''">
date_format(datelist,'%Y-%m') <= date_format(#{month},'%Y-%m')
</if>
<if test="month == null or month == ''">
date_format(datelist,'%Y-%m') <= date_format(now(),'%Y-%m')
</if>
GROUP BY MONTH
ORDER BY datelist DESC
LIMIT 0,12
) a
LEFT JOIN (
SELECT
COUNT(id) AS num,
apply_date
FROM
业务表名
WHERE
id IS NULL
GROUP BY
date_format(apply_date,'%Y-%m')
) b ON DATE_FORMAT(b.apply_date,'%Y-%m') = DATE_FORMAT(a.datelist,'%Y-%m')
ORDER BY
datelist ASC