select [缺少ID起始]=a.ID+1,[缺少ID截止]=b.ID-1 from #test a ,#test b where not exists (select ID from #test where ID=a.ID+1) and not exists (select ID from #test where ID=b.ID-1) and b.ID-1>=a.ID+1 and b.ID=(select top 1 ID from #test where ID>a.ID+1 order by ID)
declare @i Int create #TempTbl(ID Int) set @i=1 while @i<(select top 1 ID from Table1 order by ID Desc) begin insert into #TempTbl(ID) Values(@i) set @i=@i+1 end select ID from #TempTbl where ID not in(select ID from Table1) drop table #TemoTbl
实在想不出更好的办法了!!我也只会用游标了, create table AA (id int) INSERT INTO AA SELECT 1 INSERT INTO AA SELECT 2 INSERT INTO AA SELECT 4 INSERT INTO AA SELECT 5 INSERT INTO AA SELECT 6 INSERT INTO AA SELECT 8 declare @t table (id int) declare @id int declare @idd int set @idd=1 ; declare mycur cursor for select * from AA open mycur fetch next from mycur into @id while(@@fetch_status=0) begin
if(@idd=@id) begin set @idd=@id+1 end else begin insert into @t values(@idd) set @idd=@id+1 end fetch next from mycur into @id end close mycur deallocate mycur select * from @t 结果如下: id 3 7 那位大侠有更好的办法,请多多指教!!
根据楼主的提示,不用游标,这样也可以.不过还是有点麻烦!! create table AA (id int) INSERT INTO AA SELECT 1 INSERT INTO AA SELECT 2 INSERT INTO AA SELECT 4 INSERT INTO AA SELECT 5 INSERT INTO AA SELECT 6 INSERT INTO AA SELECT 8 INSERT INTO AA SELECT 9 INSERT INTO AA SELECT 10 INSERT INTO AA SELECT 11 INSERT INTO AA SELECT 13 INSERT INTO AA SELECT 14 INSERT INTO AA SELECT 15 INSERT INTO AA SELECT 17select AAA.idd from (select idd from (select (id+1)idd from AA)AAA where idd not in (select id from AA) )AAA, (select idd from (select (id-1)idd from AA)AAA where idd not in (select id from AA))BBB where AAA.idd=BBB.idd 结果如下 3 7 12 16 不知道那位高手还能想出更好的方法,多多指教
如果不是连续缺少的ID的情况,试试:select a.id-1 from AA a left join AA b on a.id=b.id+1 where b.id is null and a.id<>1
select a.id-1 from AA a left join AA b on a.id=b.id+1 where b.id is null and a.id<>1------------???
select [缺少ID起始]=a.ID+1,[缺少ID截止]=b.ID-1 from #test a ,#test b where
not exists (select ID from #test where ID=a.ID+1)
and not exists (select ID from #test where ID=b.ID-1)
and b.ID-1>=a.ID+1
and b.ID=(select top 1 ID from #test where ID>a.ID+1 order by ID)
create #TempTbl(ID Int)
set @i=1
while @i<(select top 1 ID from Table1 order by ID Desc)
begin
insert into #TempTbl(ID) Values(@i)
set @i=@i+1
end
select ID from #TempTbl where ID not in(select ID from Table1)
drop table #TemoTbl
create table AA (id int)
INSERT INTO AA SELECT 1
INSERT INTO AA SELECT 2
INSERT INTO AA SELECT 4
INSERT INTO AA SELECT 5
INSERT INTO AA SELECT 6
INSERT INTO AA SELECT 8
declare @t table (id int)
declare @id int
declare @idd int
set @idd=1 ;
declare mycur cursor for select * from AA open mycur
fetch next from mycur into @id
while(@@fetch_status=0)
begin
if(@idd=@id)
begin
set @idd=@id+1
end
else
begin
insert into @t values(@idd)
set @idd=@id+1
end
fetch next from mycur into @id
end
close mycur
deallocate mycur
select * from @t
结果如下:
id
3
7
那位大侠有更好的办法,请多多指教!!
我大概看了一眼,是这样的
Select .....ID+1 Inner Join ......
只能提示这么多思路了 ,我也在思考中.......
create table AA (id int)
INSERT INTO AA SELECT 1
INSERT INTO AA SELECT 2
INSERT INTO AA SELECT 4
INSERT INTO AA SELECT 5
INSERT INTO AA SELECT 6
INSERT INTO AA SELECT 8
INSERT INTO AA SELECT 9
INSERT INTO AA SELECT 10
INSERT INTO AA SELECT 11
INSERT INTO AA SELECT 13
INSERT INTO AA SELECT 14
INSERT INTO AA SELECT 15
INSERT INTO AA SELECT 17select AAA.idd from (select idd from (select (id+1)idd from AA)AAA where idd not in (select id from AA) )AAA,
(select idd from (select (id-1)idd from AA)AAA where idd not in (select id from AA))BBB where AAA.idd=BBB.idd 结果如下
3
7
12
16
不知道那位高手还能想出更好的方法,多多指教
on a.id=b.id+1
where b.id is null
and a.id<>1
on a.id=b.id+1
where b.id is null
and a.id<>1------------???