mysql一个SQL案例

需求 ：

　　

测试数据　

create table test1_3(id int primary key auto_increment,username varchar(10),start1 int,end1 int);
insert into test1_3(username,start1,end1) values('A',1,2);
insert into test1_3(username,start1,end1) values('A',3,4);
insert into test1_3(username,start1,end1) values('B',4,5);
insert into test1_3(username,start1,end1) values('A',6,7);
insert into test1_3(username,start1,end1) values('B',7,8);
insert into test1_3(username,start1,end1) values('A',8,9);

 

 

解决：

　　

 

解决2：

　解决代码　

　核心思想，把符合逻辑条件的行，构造相同分组

select * from test1_3;

select username,min(start1) start1,max(end1) end1 from (
select test1_3.* ,
                if(start1-1=@start1 ANd @username=username,@rn:=@rn,@rn:=@rn+1) as rn,
                @username:=username,
                @start1:=end1 as '@start'
 from test1_3 cross join (select @rn:=0,@start1:=0,@end1:=0,@username:='q') t
    order by username,start1
) t1 group by username,rn ;