Oracle:
createtable t (a number primarykey);
insertinto t (a) values (1);
insertinto t (a) values (2);
insertinto t (a) values (3);
insertinto t (a) values (100);
insertinto t (a) values (101);
insertinto t (a) values (103);
insertinto t (a) values (104);
insertinto t (a) values (105);
commit;
-- 连续范围
/*SQL 1*/
selectmin(a) start_range, max(a) end_range
from t
groupby a-rownum
orderby start_range;
/*SQL 2*/
selectmin(b.a) start_range, max(b.a) end_range
from (select a.*, a.a - rownum cc
from (select * from t orderby a) a) b
groupby b.cc
orderby start_range;
/*SQL 3*/
select nvl(lag(e) over (orderby s), minn) start_range, nvl(s,maxn) end_range
from (select lag(a,1) over (orderby a) s,a e,min(a) over() minn, max(a) over () maxn
from t)
where nvl(e - s - 1, 1) <> 0;
-- 间断范围
select s + 1 start_range, e - 1 end_range
from (select nvl(lag(a,1) over (orderby a), 0) s, a e, min(a) over () minn, max(a) over() maxn
from t)
where nvl(e - s - 1, 1) <> 0;
MySQL:
createtable t (a intprimarykey);
insertinto t values (1),(2),(3),(100),(101),(103),(104),(105);
commit;
-- 连续范围
selectmin(a) start_range, max(a) end_range
from (select a, a-rn as diff
from (select a ,@a:=@a+1 rn
from t, (select @a:=0) as a ) as b ) as c
groupby diff;
-- 间断范围
select b.v+1 start_range, b.a-1 end_range
from (select @a as v, t.a, (@a:=t.a)
from t,(select @a:=0) as z) as b
where b.a != b.v+1;