while @m > 0
begin
if exists (select d1 from #tmp where s = @m and abs(x - (select x from #tmp where s = @n and (x = 4 or x = 5))) = 1 and (x = 4 or x = 5))
begin
set @h = @h + 1
set @allid = @allid + convert(varchar, (select d1 from #tmp where s = @m and abs(x - (select x from #tmp where s = @n and (x = 4 or x = 5))) = 1 and (x = 4 or x = 5)) + ','
end
set @n = @m
set @m = @m -1
end
while @m > 0
begin
if exists (select d1 from #tmp where s = @m and abs(x - (select x from #tmp where s = @n and (x = 4 or x = 5))) = 1 and (x = 4 or x = 5))
begin
set @h = @h + 1
set @allid = @allid + convert(varchar, @m) + ','
end
set @n = @m
set @m = @m -1
end
declare @t table(d1 int, sz int)
insert into @t
select 2, 4
union all select 3, 5
union all select 4, 8
union all select 5, 1
union all select 6, 4
union all select 7, 5
union all select 8, 0
union all select 9, 4
union all select 10, 5
union all select 11, 7
union all select 12, 3
drop table #tmp
create table #tmp(
s int identity,
d1 int not null,
x int not null
)
insert into #tmp(d1, x) select d1, sz from @t order by d1 asc
declare @m int, @n int, @h int
set @m = (select max(s) from #tmp) --从最大ID开始
set @n = 0 --上一个ID
set @h = 0 --计数
while @m > 0
begin
if (select x from #tmp where s = @m and abs(x - (select x from #tmp where s = @n and (x = 4 or x = 5))) = 1 and (x = 4 or x = 5)) > 0
begin
set @h = @h + 1
end
set @n = @m
set @m = @m -1
end
如果ID是连接的,那有办法。
下面这个语句是求出所有连续4,5的下一条记录的ID。
select t1.id+1 as tNextID
from tblname as t1 inner join(
select id+1 as Nextid,sz+1 as Nextsz
from tblname
where sz=4) as t2 on t1.id=t2.id and t1.sz=t2.nextsz
抛砖引玉阿,用什么语法你自己改。
如下:
-------------------------------------
定义数据库链接部分从略了
sql="select * from 表格名"
dim kk'结果集
kk=""
rs.open
while not rs.eof
if rs("sz")=4 then
rs.movenext
if rs("sz")=5 then
rs.movenext
'45相连则取sz,结果之间用符号隔开。
kk=kk & rs("sz") & ";"
rs.movenext
end if
else
rs.movenext
end if
wend
'循环完毕,得结果集kk
'可以拆成数字
shuzu=split(kk,";")
mount=ubound(shuzu)-1'有多少结果,最后一个为空要去掉。