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
结果: