1、引言
最近才开始接触各种奇葩统计数据，想网上很多美丽大神咨询，得到一些结果。记录下来以备自己和大家共享吧
2、查询连续个月
直接贴图看看嘛
进入http://sqlfiddle.com/
需要查询的表格放左边：
/*SQLyog Ultimate v11.25 (64 bit)
MySQL - 5.5.30-tokudb-7.1.0-log
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `tt1` (
`user_id` int (11),
`start_ymd_month` varchar (21)
);
insert into `tt1` (`user_id`, `start_ymd_month`) values('18','2015-09');
insert into `tt1` (`user_id`, `start_ymd_month`) values('18','2015-08');
insert into `tt1` (`user_id`, `start_ymd_month`) values('18','2015-07');
insert into `tt1` (`user_id`, `start_ymd_month`) values('18','2015-06');
insert into `tt1` (`user_id`, `start_ymd_month`) values('18','2015-03');
insert into `tt1` (`user_id`, `start_ymd_month`) values('18','2015-01');
insert into `tt1` (`user_id`, `start_ymd_month`) values('18','1970-01');
insert into `tt1` (`user_id`, `start_ymd_month`) values('18','0000-00');
insert into `tt1` (`user_id`, `start_ymd_month`) values('19','2015-08');
insert into `tt1` (`user_id`, `start_ymd_month`) values('20','2015-08');
insert into `tt1` (`user_id`, `start_ymd_month`) values('20','2015-07');
insert into `tt1` (`user_id`, `start_ymd_month`) values('20','2015-05');
insert into `tt1` (`user_id`, `start_ymd_month`) values('20','2015-04');
insert into `tt1` (`user_id`, `start_ymd_month`) values('20','0000-00');
insert into `tt1` (`user_id`, `start_ymd_month`) values('21','2015-08');
insert into `tt1` (`user_id`, `start_ymd_month`) values('21','2015-06');
insert into `tt1` (`user_id`, `start_ymd_month`) values('21','0000-00');
insert into `tt1` (`user_id`, `start_ymd_month`) values('22','2015-08');
insert into `tt1` (`user_id`, `start_ymd_month`) values('22','2015-06');
insert into `tt1` (`user_id`, `start_ymd_month`) values('22','2015-03');
insert into `tt1` (`user_id`, `start_ymd_month`) values('22','1970-01');
insert into `tt1` (`user_id`, `start_ymd_month`) values('22','0000-00');
insert into `tt1` (`user_id`, `start_ymd_month`) values('23','2015-08');
insert into `tt1` (`user_id`, `start_ymd_month`) values('23','2015-06');
insert into `tt1` (`user_id`, `start_ymd_month`) values('23','2015-03');
sql语句放右边
select t.user_id,
min(t.start_ymd_month),
max(t.start_ymd_month),
count(t.start_ymd_month)
from (
select a.user_id,a.start_ymd_month,
@rownum=@rownum + 1 rownum,
left(a.start_ymd_month,4)*12+right(a.start_ymd_month,2) as mon
from (
select user_id,start_ymd_month
from tt1 order by 1,2
)a,
(select @rownum=0) b
) t
group by t.user_id ,t.rownum - t.mon
结果：
