获取一年里每个月的记录总数

效果：

 

数据库：

============

建表,插入数据
============

CREATE TABLE `test` (
  `id` int(4) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  `createtime` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
insert  into `test`(`id`,`name`,`createtime`) values (1,'1','2009-01-01 12:12:12'),(2,'2','2009-01-02 12:12:12'),(3,'2','2009-02-01 12:12:12'),(4,'2','2009-02-02 12:12:12'),(5,'2','2009-03-01 12:12:12'),(6,'2','2009-03-02 12:12:12'),(7,'2','2009-04-02 12:12:12');

效果：

=======
查询语句

=======

select count(id)  '总数', DATE_FORMAT(createtime,'%Y-%m') as '月份'
from test group by DATE_FORMAT(createtime,'%Y-%m')

效果

 
 注： DATE_FORMAT(date,format) 参照： http://www.w3school.com.cn/sql/func_date_format.asp