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 --*/缺号很不好弄
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)
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 9select top 1000 id=identity(int,0,1) into #2 from sysobjects a,sysobjects bselect min(a.id) as id from #2 a left join #1 b on a.id=b.id where b.id is null 5
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
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 custcust min_seq ----------- ----------- 1 3 2 2(2 行受影响)
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)
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) goselect 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
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)
写错了应该是这样 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
很好很强大,稍微修改一下: select top 1 (id +1) from #t1 where not exists(select id from #t1 aa where aa.id = #t1.id+1) order by id 还有就是表为空怎么办,楼主没有说明。
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
--*/缺号很不好弄
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)
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 9select top 1000 id=identity(int,0,1) into #2 from sysobjects a,sysobjects bselect min(a.id) as id from #2 a left join #1 b on a.id=b.id
where b.id is null 5
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
cust seq
----------- -----------
1 0
2 1
3 2
4 3
5 4
7 6
8 7
10 9
-----------
5
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 custcust min_seq
----------- -----------
1 3
2 2(2 行受影响)
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)
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)
goselect
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
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)
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
select top 1 (id +1) from #t1 where not exists(select id from #t1 aa where aa.id = #t1.id+1)
order by id
还有就是表为空怎么办,楼主没有说明。