MySQL解决连续个月问题

最新推荐文章于 2024-08-07 13:33:13 发布

转载最新推荐文章于 2024-08-07 13:33:13 发布 · 681 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/493000

文章标签：

#数据库

本文介绍了一种使用SQL查询特定用户连续月份记录的方法，并通过实例演示了如何实现这一目标。适用于需要处理时间序列数据的场景。

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

结果：