SELECT
DATE_FORMAT(jlsj,'%Y-%m')AS ny,
IFNULL(COUNT(*),0)AS count
FROM ywgl_sjgl01
WHERE jlsj BETWEEN DATE_SUB(CURDATE(),INTERVAL12MONTH)AND CURDATE()GROUPBY ny
orderby ny desc
但是需要查寻近12个月的年月和数量(缺少2023-05、2023-01、2022-12)
SELECT
DATE_FORMAT(@cdate := date_add(@cdate,INTERVAL-1MONTH),'%Y-%m') ny
FROM(SELECT@cdate := date_add( CURDATE(),INTERVAL1MONTH)FROM ywgl_sjgl01 LIMIT12) a
orderby ny desc
group by分组后查询无数据补0,最后结果:
SELECT
sj.date ny,
IFNULL( sl.count,0) count
FROM(SELECT
DATE_FORMAT(@cdate := date_add(@cdate,INTERVAL-1MONTH),'%Y-%m')dateFROM(SELECT@cdate := date_add( CURDATE(),INTERVAL1MONTH)FROM ywgl_sjgl01 LIMIT12) a
) sj
LEFTJOIN(SELECT
DATE_FORMAT(jlsj,'%Y-%m')ASdate,
IFNULL(COUNT(*),0)AS count
FROM ywgl_sjgl01
WHERE jlsj BETWEEN DATE_SUB(CURDATE(),INTERVAL12MONTH)AND CURDATE()GROUPBYdate) sl ON sl.date= sj.dateorderby ny desc