求一sql語句

[TTLOVEYOU3344]

假如有個表 a，ID VARCHAR(9)
id
GMN000001
GMN000002
GMN000003
GMN000004
GMN000005
GMN000006
GMN000008
ABS000008
我要找的就是把中間的斷碼找出來，上面的數據，應該要顯示GMN000007，注意ID都是GMN開頭(其他字符開頭的不能出現在結果集裡)的，
怎麼才能把所有的中間缺碼的找出來？

[TTLOVEYOU3344]

由於這個表的資料比較大，希望老大給我一個好的算法，如果有不清楚的地方，[e-mail]我在線等，比較急，老大們多多幫忙啊!

[NinGoo]

顶

[iswear428]

--构建一个临时表，存储自动编号
declare @max int
declare @min int

select max(substring(id,3,len(id)-1)) into @max,min(substring(id,3,len(id)-1)) into @min
from table1

declare @temp table(id varchar(20) )
while @max>=@min
begin
insert into @temp values('GMN'+left('000000'+convert(varchar(6),@max),6))
set @max =@max-1
end

--获得被删除的ID
select id
from @temp
where id not in(select id from table1)

[mediafang2003]

关注！

[Andy__Huang]

create table tb(id varchar(20))
Insert into tb
select 'GMN000001'
union all select 'GMN000002'
union all select 'GMN000003'
union all select 'GMN000004'
union all select 'GMN000005'
union all select 'GMN000006'
union all select 'GMN000008'
union all select 'ABS000008'

select * from
(select ID=left(id,3)+replicate('0',6-len(cast(right(id,6) as numeric)+1))+cast(cast(right(id,6) as numeric)+1 as varchar) from tb where left(id,3)='GMN')a
where not exists(select id from tb where id=a.id) and id<=(select max(id) from tb where left(id,3)='GMN')

--結果：
id
GMN000007

[TTLOVEYOU3344]

問題解決，謝謝 iswear428跟hdhai9451，馬上結貼!

[子陌红尘]

需要借助临时表，一旦出现连续的断码，楼上的方法就不能用了。

[vinsonshen]

select 'GMN'+right(1000000+a.id+1,6) from
(select cast(right(id,len(id)-len('GMN')) as int) as id from t_test where left(id,3)='GMN') as a
left join
(select min(cast(right(id,len(id)-len('GMN')) as int)) as id from t_test where left(id,3)='GMN'
union all
select max(cast(right(id,len(id)-len('GMN')) as int)) from t_test where left(id,3)='GMN') as b
on a.id=b.id
where b.id is null and not exists (select * from t_test where left(id,3)='GMN' and right(id,len(id)-len('GMN'))=a.id+1)