/* -- Author:Flystone -- Date:2008-05-15 -- Version:V1.001 */-- Test Data: Ta If object_id('Ta') is not null Drop table Ta Go Create table Ta(id varchar(8)) Go Insert into Ta select 'a1' union all select 'a2' union all select 'a3' union all select 'a4' union all select 'a6' union all select 'a7' union all select 'a8' union all select 'a9' union all select 'a10' union all select 'a13' union all select 'a14' Go --Startselect 'a'+ltrim(stuff(id,1,1,'')+1) from ta where stuff(id,1,1,'') +1 not in (select stuff(id,1,1,'') as id from ta) --Result: /* ------------- a5 a11 a15(所影响的行数为 3 行) */ --End
--> 测试数据: # if object_id('tempdb.dbo.#') is not null drop table # create table # (id int) insert into # select 1 union all select 2 union all select 3 union all select 4 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 13 union all select 14select id+1 as first_id, id2-1 as last_id from ( select id,(select min(id) from # where id>t.id)id2 from # as t ) as t where id2-id>1/* first_id last_id ----------- ----------- 5 5 11 12 */
/* -- Author:Flystone -- Date:2008-05-15 -- Version:V1.001 */-- Test Data: Ta If object_id('Ta') is not null Drop table Ta Go Create table Ta(id varchar(8)) Go Insert into Ta select 'a1' union all select 'a2' union all select 'a3' union all select 'a4' union all select 'a6' union all select 'a7' union all select 'a8' union all select 'a9' union all select 'a10' union all select 'a13' union all select 'a14' Go --Startselect 'a'+ltrim(ltrim(a.new_id)) from (select b.id*1000 + c.id *100 + d.id * 10 + e.id as new_id from (select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 0) b, (select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 0) c, (select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 0) d, (select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 0) e ) a where a.new_id not in( select cast(stuff(id,1,1,'') as int) from ta) and new_id < (select max( cast(stuff(id,1,1,'') as int)) from ta) and new_id <>0 order by new_id --Result: /* ------------- a5 a11 a12(所影响的行数为 3 行)*/ --End
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(id varchar(8))
Go
Insert into Ta select 'a1' union all
select 'a2' union all
select 'a3' union all
select 'a4' union all
select 'a6' union all
select 'a7' union all
select 'a8' union all
select 'a9' union all
select 'a10' union all
select 'a13' union all
select 'a14'
Go
--Startselect 'a'+ltrim(stuff(id,1,1,'')+1)
from ta
where stuff(id,1,1,'') +1 not in (select stuff(id,1,1,'') as id from ta)
--Result:
/*
-------------
a5
a11
a15(所影响的行数为 3 行)
*/
--End
if object_id('tempdb.dbo.#') is not null drop table #
create table # (id int)
insert into #
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 13 union all
select 14select id+1 as first_id, id2-1 as last_id from
(
select id,(select min(id) from # where id>t.id)id2 from # as t
) as t
where id2-id>1/*
first_id last_id
----------- -----------
5 5
11 12
*/
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(id varchar(8))
Go
Insert into Ta select 'a1' union all
select 'a2' union all
select 'a3' union all
select 'a4' union all
select 'a6' union all
select 'a7' union all
select 'a8' union all
select 'a9' union all
select 'a10' union all
select 'a13' union all
select 'a14'
Go
--Startselect 'a'+ltrim(ltrim(a.new_id)) from (select b.id*1000 + c.id *100 + d.id * 10 + e.id as new_id from
(select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 0) b,
(select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 0) c,
(select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 0) d,
(select 1 as id union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 0) e
) a
where a.new_id not in( select cast(stuff(id,1,1,'') as int) from ta)
and new_id < (select max( cast(stuff(id,1,1,'') as int)) from ta) and new_id <>0
order by new_id
--Result:
/*
-------------
a5
a11
a12(所影响的行数为 3 行)*/
--End