求最多连续编号
selec   *   from   f()
结果为
id     num
1         1
2         2
3         2
4         2
5         15
6         123
7         0
8         2
9         2
10       10
..       ..
50       89

num为不确定的整数,求:NUM为2(或者其它值)的最多连续记录,
比如:
max   startid   endid
3             2             4



------解决方案--------------------
declare @t1 table (
id0 int ,
id int,
num int
)
insert @t1(id,num)
select a.* from f a left join f b on b.id=a.id-1 where a.num <> b.num
order by a.id


declare @t2 table (
id0 int ,
id int,
num int
)
insert @t2(id,num)
select a.* from f a left join f b on b.id=a.id+1 where a.num <> b.num
order by a.id

select b.id-a.id as [max],a.id as startid,b.id as endid,a.num
from @t1 a,@t2 b
where a.id0=b.id0
and not exists (
seelct 1
from @t1 a1,@t2 b1
where a1.id0=b1.id0
and b1.id-a1.id> b.id-a.id
)

------解决方案--------------------
declare @t table(
id int,
num int)
insert @t
select 1, 1 union all
select 2, 2 union all
select 3, 2 union all
select 4, 2 union all
select 5, 15 union all
select 6, 123 union all
select 7, 0 union all
select 8, 2 union all
select 9, 2 union all
select 10,10 union all
select 50,89

-- 查询
declare @num int
set @num = 2 -- 要查询的 @num
select top 1
[max]= endid - startid + 1, *
from(
select
startid = a.id,
endid = (
select top 1 id from @t b
where num = a.num
and id> =a.id
and not exists(
select * from @t
where num=b.num
and id = b.id + 1
)
)
from @t a
where num = @num
and not exists(
select * from @t
where num=a.num
and id = a.id - 1)
)a
order by [max] desc

-- 结果:
max startid endid
----------- ----------- -----------
3 2 4

(1 行受影响)