SELECT yue.m AS'月份',IFNULL(a.num,0) AS'数量',IFNULL(a.OPERATION_STATE,'') AS'操作类型'FROM
(
SELECT1AS m
UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9UNIONALLSELECT10UNIONALLSELECT11UNIONALLSELECT12
) yue LEFTJOIN (SELECTMONTH(OPERATION_TIME) AS m,COUNT(*) AS num,OPERATION_STATE FROM mytable
WHEREYEAR(OPERATION_TIME)=2016AND OPERATION_STATE = 'del'GROUPBYMONTH(OPERATION_TIME) ) a ON yue.m=a.m