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 +-------+-----+
結果
