/*--查询新编号的示例
要求:
按id前4位分组,查询出最小一组的缺号,如果没有,则用最大组的id+1
*/
--测试数据
create table tb(id int)
insert tb select 10010001
union all select 10010002
union all select 10010003
union all select 10010004
union all select 10010005
--union all select 10020001
union all select 10020002
union all select 10020003
union all select 10020004
union all select 10030001
go
--查询处理1(最小编号为:xxxx0001的处理)
select 新id=left(id,4)+right(10001+right(a.id,4),4)
from(
select id=isnull(min(a.id),(select max(id) from tb))
from(
select id from tb
union all
select distinct left(id,4)+'0000' from tb
)a left join(
select id=max(id) from tb group by left(id,4)
)b on a.id=b.id
where b.id is null and not exists(
select * from tb where id=left(a.id,4)+right(10001+right(a.id,4),4))
)a
--查询处理2(最小编号为表中每组的最小编号)
select 新id=left(id,4)+right(10001+right(a.id,4),4)
from(
select id=isnull(min(a.id),(select max(id) from tb))
from tb a left join(
select id=max(id) from tb group by left(id,4)
)b on a.id=b.id
where b.id is null and not exists(
select * from tb where id=left(a.id,4)+right(10001+right(a.id,4),4))
)a
go
--删除测试
drop table tb
/*--测试结果
新id
----------------
10020001
（所影响的行数为 1 行）
新id
----------------
10030002
（所影响的行数为 1 行）
--*/
