sql连续记录,查中间断掉的记录
比如我们有一个资料按0---10000 编号，如果中间有漏掉的编号，需要填补，怎么办呢?
一次取得 select f1 from t1 order by f1 所有记录 升序
first
p1 := fieldbyname('f1').asinteger;
while not eof do
p2 := fieldbyname('f1').asinteger;
if p2 > p1 + 1 then //lost
begin
for i:= p1 + 1 to p2 do
insert into t1 values(i);
end;
p1 := p2 ;
next;
end;
===================
用sql实现举例
use tempdb
go
drop table t1
create table t1 (f1 integer)
insert into t1 values(0)
insert into t1 values(1)
insert into t1 values(5)
declare @p1 integer,@p2 integer,@i integer
declare cur1 cursor for select f1 from t1 order by f1 asc
open cur1
fetch cur1 into @p1
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH cur1 into @p2
if ( @@FETCH_STATUS = 0 )
begin
if (@p2 > @p1 + 1)
begin
select @i = @p1 + 1
while @i < @p2
begin
print @i
insert into t1 values(@i)// 插入记录
set @i = @i + 1
end
end
end
select @p1 = @p2
END
CLOSE cur1
DEALLOCATE cur1
