SQL统计查询（按月份）

SQL按月统计查询

建表，添加数据

/*Table structure for table `mytable` */

DROP TABLE IF EXISTS `mytable`;

CREATE TABLE `mytable` (
  `ID` int(11) DEFAULT NULL,
  `USERID` varchar(32) DEFAULT NULL,
  `OPERATION_STATE` varchar(32) DEFAULT NULL,
  `OPERATION_TIME` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `mytable` */

insert  into `mytable`(`ID`,`USERID`,`OPERATION_STATE`,`OPERATION_TIME`) values (1,"U1","add","2016-01-21 12:01:50"),(2,"U1","del","2016-01-24 12:01:50"),(2,"U1","del","2016-02-24 12:01:50"),(2,"U1","del","2016-03-24 12:01:50"),(2,"U1","del","2016-03-23 12:01:50"),(2,"U1","del","2016-03-25 12:01:50"),(2,"U1","del","2016-04-10 12:01:50"),(2,"U1","del","2016-04-11 12:01:50"),(2,"U1","del","2016-04-12 12:01:50"),(2,"U1","del","2016-04-15 12:01:50"),(2,"U1","del","2016-04-18 12:01:50"),(2,"U1","del","2016-05-18 12:01:50"),(2,"U1","del","2016-07-18 12:01:50"),(2,"U1","del","2016-07-19 12:01:50"),(2,"U1","del","2016-07-29 12:01:50"),(2,"U1","del","2016-08-29 12:01:50"),(2,"U1","del","2016-10-10 12:01:50"),(2,"U1","del","2016-10-11 12:01:50"),(2,"U1","del","2016-10-15 12:01:50"),(2,"U1","del","2016-10-25 12:01:50"),(2,"U1","del","2016-10-14 12:01:50"),(2,"U1","del","2016-12-14 12:01:50");

查询每月del操作次数。没有del操作的月份要显示出来为 0

SELECT yue.m AS "月份",IFNULL(a.num,0) AS "数量",IFNULL(a.OPERATION_STATE,"") AS "操作类型"
FROM
(
SELECT 1 AS m
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
UNION ALL
SELECT 11
UNION ALL
SELECT 12
) yue LEFT JOIN (SELECT MONTH(OPERATION_TIME) AS m,COUNT(*) AS num,OPERATION_STATE FROM mytable
  WHERE YEAR(OPERATION_TIME)=2016 AND OPERATION_STATE = "del"
GROUP BY MONTH(OPERATION_TIME) ) a ON yue.m=a.m

结果