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

结果