首先创建这样一个表
- create table range_problem(
- a int(10) unsigned not null,
- primary key (a));
插入以下测试数据
- insert into range_problem values(1);
- insert into range_problem values(2);
- insert into range_problem values(3);
- insert into range_problem values(48);
- insert into range_problem values(50);
- insert into range_problem values(51);
- insert into range_problem values(52);
- insert into range_problem values(53);
- insert into range_problem values(66);
- insert into range_problem values(67);
如何将这些结果按范围输出呢?
结果为:
![](http://img.blog.itpub.net/blog/attachment/201408/21/29773961_140860246155lE.jpg?x-oss-process=style/bb)
【思路】
方案一及解决思路
:
首先做一个这样的返回集
![](http://img.blog.itpub.net/blog/attachment/201408/21/29773961_14086025042hAZ.jpg?x-oss-process=style/bb)
- select a,@a:=@a+1 as b
- from range_problem,(select @:a=0) as a;
再做另一个返回集:(通过返回的结果来看,发现一组连续值内,连续数值的差是一个常量 )
![](http://img.blog.itpub.net/blog/attachment/201408/21/29773961_140860254257qv.jpg?x-oss-process=style/bb)
解决方案:
- select a,b,a-b as diff
- from(
- select a,@a:=@a+1 b
- from range_problem,
- (select @a:=0) as a
- ) as b;
由于a-b为固定值,我们可以将通过为其分组,来实现最后的范围
- select min(a) start_range, max(b) end_range
- from(select a,b,a-b as diff
- from(select a,@a:=@a+1 as b
- from range_problem,(select @a:=0) as a
- ) as b
- ) as c
- group by diff;
方案二及解决思路:(通过子查询)
比如1~3的范围,最大值应该是3。
方法是,大于或等于当范围值,且最后一个值为间断的最小值。
- select a,(
- select min(a)
- from range_problem a
- where not exists (select *
- from range_problem b
- where a.a+1 = b.a)
- and a.a >= c.a) max
- from range_problem c;
![](http://img.blog.itpub.net/blog/attachment/201408/21/29773961_1408602642Ro6O.jpg?x-oss-process=style/bb)
最后只需要再将max列分组,然后获得最大值和最小值就是我们要的结果了:
- select min(a) start_range,max(a) end_range
- from(select a,
- (select min(a)
- from range_problem a
- where not exists (select *
- from range_problem b
- where a.a+1 = b.a)
- and a.a >= c.a) max
- from range_problem c) d
- group by max;
但此解决方案的扫描成本变为O(N^2),对于表中数据量很大的情况,其性能则会变得十分糟糕。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29773961/viewspace-1248574/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29773961/viewspace-1248574/