TSQL:判定一段数组连续的数字段有多少的方案

给定了一列数字,需要判定该列中连续的数据字有多少条记录:

field1,field2
123,1   123-1
124,2   124-2
125,3   125-3
127,4
128,5
create table tbl(
field1 int,
field2 int
)
insert into tbl(field1,field2) values(123,1);
insert into tbl(field1,field2) values(124,2);
insert into tbl(field1,field2) values(125,3);
insert into tbl(field1,field2) values(127,4);
insert into tbl(field1,field2) values(128,5);
insert into tbl(field1,field2) values(159,6);
insert into tbl(field1,field2) values(160,7);
insert into tbl(field1,field2) values(167,8);
select * from tbl

select t10.field1,t10.field2,t11.delta,t11.cou
from (select field1,field2,field1-field2 as delta from tbl) t10
inner join(
select delta,count(0) as cou from (select field1,field2,field1-field2 as delta from tbl) tbl_t group by delta
) t11 on t10.delta=t11.delta

 

posted @ 2018-03-26 14:33  cctext  阅读(185)  评论(0编辑  收藏  举报