求一个SQL语句

Question

有一个表Table1有个字段ID 为varchar 型
现在里面有如下ID 12345002000
 12345781002
 12345006888
 12345004678
 12345123000
 12345007123
 ............
 12345001000我现在要找前五位为12345 后三位不定,也就是中间三位里面不连续的最大的那条记录中间三位比如为:
 001
 006
 004
 002
 那么找出的不连续的最大的记录为002
因为没有003!
如何高效找出那条记录呢?

Answer 1

create table tt(id varchar(100))
insert tt select  '12345002000'
union all select '12345781002'
union all select '12345006888'
union all select '12345004678'
union all select '12345123000'
union all select '12345007123'select * from tt 
where 
left(id,5)+right('00'+convert(varchar,convert(int,substring(id,6,3))+1),3) 
not in
(select left(id,5)+right('00'+convert(varchar,convert(int,substring(id,6,3))),3) from tt) drop table tt
/*
id                                                                                                   
--------------------------------------
12345002000
12345781002
12345004678
12345123000
12345007123
*/

Answer 2

declare @a table
(
ID varchar(11)
)
insert into @a
select '12345002000' union all
select '12345003002' union all
select '12345006888' union all
select '12345004678' union all
select '12345123000' union all
select '12345007123' union all
select '12345001000'
select idx=identity(int,1,1),id into # from (select top 1000 * from @a order by substring(id,6,3))aselect max(id)
from #
where substring(id,6,3)<
(
select substring(min(id),6,3) from # a where exists(select id from # where a.idx=idx+1 and (cast(substring(id,6,3)as int)+1)<>(cast(substring(a.id,6,3)as int)))
)
drop table #/*
12345004678
*/

Answer 3

select min([ID]) from Table1 A 
where convert (int ,substring(A.[ID],6,3))+1 not in (select convert (int ,substring 
   (Table1.[ID],6,3)) from Table1)

Answer 4

借用夜遊神的測試環境:
declare @a table
(
ID varchar(11)
)
insert into @a
select '12345002000' union all
select '12345003002' union all
select '12345006888' union all
select '12345004678' union all
select '12345123000' union all
select '12345007123' union all
select '12345001000'---
select min([ID]) from @a a where convert (int ,substring(a.[ID],6,3))+1 not in (select convert (int ,substring(b.[ID],6,3)) from @a b) --
The result:
  12345004678

Answer 5

declare @a table
(
ID varchar(11)
)
insert into @a
select '12345002000' union all
select '12345003002' union all
select '12345006888' union all
select '12345004678' union all
select '12345123000' union all
select '12345007123' union all
select '12345001000'select min(id) from @a b where not exists (select 1 from @a where convert (int ,substring(b.[ID],6,3))+1 = convert (int ,substring([ID],6,3)))

Answer 6

001
 006
 004
 002--002是一个，那么，004,006算不算？如果算，那么可以这样实现：
--目标：找出不连续数据的最大值
--建表
Create table Test(
id char(11)
)
insert into Test
select 
 '12345002000'
union all select
 '12345781002'
union all select
 '12345006888'
union all select
 '12345004678'
union all select
 '12345123000'
union all select
 '12345007123'
union all select
 '12345001000'--查询
select right('000'+cast((case when b.id is null then a.id-1 end) as varchar(10)),3)
from (
select ID = cast(substring(ID,6,3)as int)+1
from test
where left(id,5)= '12345') a
left join
(
select ID = cast(substring(ID,6,3)as int)
from test
where left(id,5)= '12345'
) b
on a.ID = b.ID
where b.id is null

Answer 7

也就是说，先给ID加1，再和原来的ID比较，
如果加1后的ID在原表中不存在，那么，它就是最大的值 。