如何快速查找可用编号空位 在线等待 大师请出手

[lliang0488]

有一 Table , Field(cust,seq(int 型)，根据 cust 分组新增一笔记录时 int 递增加1，由0开始加，当有记录删除时，新增时自动找出被删除的seq 最小号来，如何用存储过程实现啊？
如 0,1,2,3,4,6,7,9 找出 5 这个最小空号来，而不是找8

[wzy_love_sly]

CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO

--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
	end_col2=(
		SELECT MIN(col2) FROM tb aa
		WHERE col1=a.col1 AND col2>a.col2 
			AND NOT EXISTS(
				SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
		-1
FROM(
	SELECT col1,col2 FROM tb
	UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
	SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
	AND NOT EXISTS(
		SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          1           1
a          4           5
b          2           4
--*/

缺号很不好弄

[viva369]

create table #t1
(
	id int
)
insert #t1 select 1
insert #t1 select 2
insert #t1 select 5
insert #t1 select 6
insert #t1 select 7
go
select top 1 (id +1) from #t1 where not exists(select id from #t1 aa where aa.id = #t1.id+1) 

[wzy_love_sly]

create table #1 (id int)
insert into #1 select 0
insert into #1 select 1
insert into #1 select 2
insert into #1 select 3
insert into #1 select 4
insert into #1 select 6
insert into #1 select 7
insert into #1 select 9

select top 1000 id=identity(int,0,1) into #2 from sysobjects a,sysobjects b

select min(a.id) as id from #2 a left join #1 b on a.id=b.id
where b.id is null 

5

[青锋-SS]

create table tb(cust int,seq int)
go
declare @i int
set @i=0
while @i<10
begin
--0,1,2,3,4,6,7,9
if @i not in(5,8) insert into tb select @i+1,@i
set @i=@i+1
end 
go
select * from tb
go
select sn=identity(int,0,1),seq into #t from tb
select min(sn) from #t where sn<>seq
go
drop table tb,#t
go

[青锋-SS]

--结果
cust        seq
----------- -----------
1           0
2           1
3           2
4           3
5           4
7           6
8           7
10          9


-----------
5

[kk19840210]

create table #tab(cust int,seq int)

insert into #tab values(1,0)

insert into #tab values(1,1)

insert into #tab values(1,2)

insert into #tab values(1,4)

insert into #tab values(1,6)

insert into #tab values(1,7)
insert into #tab values(2,0)

insert into #tab values(2,1)

insert into #tab values(2,3)

insert into #tab values(2,4)

insert into #tab values(2,5)

insert into #tab values(2,7)

select top 1000 id=identity(int,0,1) into #id from syscolumns a,sysobjects b
select cust,min_seq=(select min(id) from #id where id not in(select seq from #tab where cust=a.cust)) from #tab a group by cust



cust        min_seq
----------- -----------
1           3
2           2

(2 行受影响)

[flairsky]

create table #t (id int)
insert into #t
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 10 

select distinct a.id+1 from #t a,#t b where b.id-a.id>=2 and a.id+1 not in (select id from #t) 

[flairsky]

create table #t (id int)
insert into #t
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 10 

select min (t.id ) from (select a.id+1 as id from #t a,#t b where b.id-a.id>=2 and a.id+1 not in (select id from #t) )as t

[子陌红尘]

--只要考虑清楚处理首位编号断号、中间断号和编号完整三种情况，处理并不复杂：

create table Field(cust varchar(8),seq int)
insert into Field values('aa',0)
insert into Field values('aa',1)
insert into Field values('aa',2)
insert into Field values('aa',3)
insert into Field values('aa',4)
insert into Field values('aa',6)
insert into Field values('aa',7)
insert into Field values('aa',9)
insert into Field values('bb',1)
insert into Field values('bb',2)
insert into Field values('bb',3)
insert into Field values('bb',5)
insert into Field values('bb',6)
insert into Field values('bb',8)
insert into Field values('cc',0)
insert into Field values('cc',1)
insert into Field values('cc',2)
insert into Field values('cc',3)
insert into Field values('cc',4)
insert into Field values('cc',5)
go

select 
    a.cust,min(a.seq) as min_seq
from 
    (select 
         t.cust,(case when exists(select 1 from Field where seq=0 and cust=t.cust) then t.seq+1 else 0 end) as seq 
     from 
         Field t 
     where 
         not exists(select 1 from Field where cust=t.cust and seq=t.seq+1)) a 
group by 
    a.cust

/*
cust     min_seq     
-------- -----------
aa       5
bb       0
cc       6
*/

drop table Field
go

[internetroot]

8楼的也可以这样做，不要使用min
select b.id+1 from #t a,#t b where a.id-b.id=2
and b.id+1 not in (select id from #t)

[internetroot]

写错了应该是这样

select top 1 b.id+1 from #t a,#t b where a.id-b.id=2
 and b.id+1 not in (select id from #t) order by b.id+1

[正宗老冉]

空记录表给1，一般。

[cson_cson]

[Quote=引用 2 楼 viva369 的回复:]
SQL code
create table #t1
(
id int
)
insert #t1 select 1
insert #t1 select 2
insert #t1 select 5
insert #t1 select 6
insert #t1 select 7
go
select top 1 (id +1) from #t1 where not exists(select id from #t1 aa where aa.id = #t1.id+1)
[/Quote]
很好很强大，稍微修改一下：
select top 1 (id +1) from #t1 where not exists(select id from #t1 aa where aa.id = #t1.id+1)
order by id
还有就是表为空怎么办，楼主没有说明。