/*--查詢新編號的示例

	要求:
	按id前4位分組,查詢出最小一組的缺號,如果沒有,則用最大組的id+1

--鄒建 2004.12(引用請保留此信息)--*/

--測試數據
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 行）
--*/