SQL查找大小为n的连续区间
数据准备
1 create table sequence 2 ( 3 seq int not null primary key 4 ); 5 6 insert into values(3); 7 insert into values(4); 8 insert into values(5); 9 insert into values(6); 10 insert into values(7); 11 insert into values(8); 12 insert into values(10); 13 insert into values(11); 14 insert into values(15); 15 insert into values(16); 16 insert into values(17); 17 insert into values(20); 18 insert into values(22); 19 insert into values(23); 20 insert into values(24); 21 insert into values(28); 22 insert into values(30); 23 insert into values(31);
如何要查找长度为4的区间首数和尾数，即@n=4
解决方案一：
select S1.seq as start,S1.seq+@n-1 as end from sequence as S1 cross join sequence as S2 cross join sequence as S3 where S2.seq = S1.seq+@n-1 group by S1.seq having sum(case when S3.seq >S1.seq and S3.seq<=S2.seq then 1 else 0 end)=3;
View Code
此方案用到了自联接，where条件决定了起点和终点，group by 子句用于生成递归集合，还需要一个能够描述区间内部各个点需要满足的条件，为此增加一个自联接表S3，having子句针对子集内元素个数作出约束，符合约束的集合将被取出。
1 +-------+-----+ 2 | start | end | 3 +-------+-----+ 4 | 3 | 6 | 5 | 4 | 7 | 6 | 5 | 8 | 7 | 15 | 18 | 8 +-------+-----+
结果
