selec * from f()
结果为
kid id num
01 1 1
01 2 2
01 3 2
01 4 2
01 5 15
01 6 123
01 7 0
01 8 2
01 9 2
01 10 10
01 .. ..
01 50 89
02 .. ..
.. .. ..
.. .. ..num为不确定的整数,id不确定递增1,
求:NUM为2(或者其它值)的最多连续记录,
比如:
max kid startid endid num
3 01 2 4 2
结果为
kid id num
01 1 1
01 2 2
01 3 2
01 4 2
01 5 15
01 6 123
01 7 0
01 8 2
01 9 2
01 10 10
01 .. ..
01 50 89
02 .. ..
.. .. ..
.. .. ..num为不确定的整数,id不确定递增1,
求:NUM为2(或者其它值)的最多连续记录,
比如:
max kid startid endid num
3 01 2 4 2
kid char(2),
id int,
num int)
insert @t
select '01', 1, 1 union all
select '01', 2, 2 union all
select '01', 3, 2 union all
select '01', 4, 2 union all
select '01', 5, 15 union all
select '01', 6, 123 union all
select '01', 7, 0 union all
select '01', 8, 2 union all
select '01', 9, 2 union all
select '01', 10,10 union all
select '01', 50,89-- 查询
declare @num int
set @num = 2 -- 要查询的 @num
select top 1
[max]= endid - startid + 1,
kid, startid, endid, num
from(
select
kid, num,
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
----------- ---- ----------- ----------- -----------
3 01 2 4 2(1 行受影响)
declare @t table(
kid char(2),
id int,
num int)
insert @t
select '01', 1, 1 union all
select '01', 2, 2 union all
select '01', 3, 2 union all
select '01', 4, 2 union all
select '01', 5, 15 union all
select '01', 6, 123 union all
select '01', 7, 0 union all
select '01', 8, 2 union all
select '01', 9, 2 union all
select '01', 10,10 union all
select '01', 50,89 union all
select '02', 1, 1 union all
select '02', 2, 2 union all
select '02', 3, 2 union all
select '02', 4, 2 union all
select '02', 5, 2 union all
select '02', 6, 123 union all
select '02', 7, 0 union all
select '02', 8, 2 union all
select '02', 9, 2 union all
select '02', 10,10 union all
select '02', 50,89应该得出的结果
4 02 2 5 2