mysql查询近六个月的数据
一、查询近六个月二、绑定业务数据
·
一、查询近六个月
SELECT date_format( @lastDay := last_day( date_add( @lastDay, INTERVAL 1 MONTH ) ), '%Y-%m' ) month
FROM( SELECT @lastDay := date_add( curdate( ), INTERVAL - 6 MONTH ) FROM PM_FUNDS_EXIST LIMIT 6 ) a
二、绑定业务数据
SELECT DATE_TABLE.LASTDAYS AS year,IFNULL(TEMP.COUNT, 0 ) AS count,2 category FROM
(SELECT DATE_FORMAT(@LASTDAY := LAST_DAY( DATE_ADD(@LASTDAY,INTERVAL 1 MONTH ) ) ,'%Y-%m' ) LASTDAYS FROM (SELECT @LASTDAY :=
DATE_ADD(CURDATE(),INTERVAL -6 MONTH) FROM PM_FUNDS_EXIST LIMIT 6) A ) DATE_TABLE
LEFT JOIN( SELECT DATE_FORMAT(P.CREATE_TIME,'%Y-%m') AS year, SUM(EQUIPMENT_FEE+MATERIAL_FEE)count FROM PM_FUNDS_EXIST P
WHERE CATEGORY=2 GROUP BY DATE_FORMAT(P.CREATE_TIME,'%Y-%m')
)TEMP ON TEMP.YEAR = DATE_TABLE.LASTDAYS
更多推荐
已为社区贡献1条内容
所有评论(0)