set rowcount 200
select identity(int,1,1) as id into # from sysobjects
set rowcount 0
select [单号]=a.单号+b.id
from
(select 日期,单号=10000 from tb group by 日期
) a,# b
where not exists(select 1 from tb where 日期=a.日期 and 单号=a.单号+b.id)
and exists(select 1 from tb where 日期=a.日期 and 单号>a.单号+b.id)
CREATE function dbo.GetNo ()
returns @re table(intNo int)
as
begin
declare @intMinNo int
declare @intMaxNo int
select @intMinNo=min(intNo),@intMaxNo=max(intNo) from tb
while @intMinNo<@intMaxNo
begin
set @intMinNo=@intMinNo+1
if not exists(select intNo from tb where intNo=@intMinNo)
insert into @re select @intMinNo
end
return
end
--测试:
create table tb(chrDate char(8),intNo int)
insert into tb select '20051001','10001'union all
select '20051001','10003'union all
select '20051001','10005'union all
select '20051001','10008'
select * from tb
create table t(日期 datetime,单号 int)
insert into t select '20051001',10001
insert into t select '20051001',10003
insert into t select '20051001',10005
insert into t select '20051001',10008
--执行处理过程
set rowcount 1000
select identity(int,0,1) as id into #T from sysobjects a,sysobjects b
set rowcount 0
select
单号 = a.单号+b.id
from
(select 日期,单号=min(单号) from t group by 日期) a,
#T b
where
not exists(select 1 from t where 日期=a.日期 and 单号=a.单号+b.id)
and
exists(select 1 from t where 日期=a.日期 and 单号>a.单号+b.id)
create table tb(日期 char(8),单号 char(5))
insert tb
select '20051001','10001' union all
select '20051001','10003' union all
select '20051001','10005' union all
select '20051001','10008'
go
--已知道日期,不知道单号的最大和最小,求断号:10002,10004,10006,10007
create procedure p_1
@date char(8)
as
declare @num1 char(5),@num2 char(5),@i int
select @num1=min(单号),@num2=max(单号) from tb where 日期=@date
create table #(日期 char(8),单号 char(5))
set @i=cast(@num1 as int)
while @i<=cast(@num2 as int)
begin
if not exists(select 1 from tb where 日期=@date and 单号=cast(@i as char(5)))
insert into #(日期,单号)values(@date,@i)
set @i=@i+1
end
select 单号 from #
go
exec p_1 '20051001'
drop table tb
drop procedure p_1
/*
单号
-----
10002
10004
10006
10007
--生成测试数据
create table t(日期 datetime,单号 int)
insert into t select '20051001',10001
insert into t select '20051001',10003
insert into t select '20051001',10005
insert into t select '20051001',10008
--执行处理过程
set rowcount 1000
select identity(int,0,1) as id into #T from sysobjects a,sysobjects b
set rowcount 0
select
单号 = a.单号+b.id
from
(select 日期,单号=min(单号) from t group by 日期) a,
#T b
where
not exists(select 1 from t where 日期=a.日期 and 单号=a.单号+b.id)
and
exists(select 1 from t where 日期=a.日期 and 单号>a.单号+b.id)
--测试表及数据
create table t(日期 datetime,单号 int)
insert into t select '20051001',10001
insert into t select '20051001',10003
insert into t select '20051001',10005
insert into t select '20051001',10008
--辅助函数
CREATE FUNCTION dbo.fnt_getAllNum()
RETURNS @tbl table
( item_id int identity(1,1))
as
/*效率不高,楼主可根据实际情况修改,主要是返回一个连续id的结果集*/
begin
declare @count int
select @count=right(max(单号),4) from t
while @count>0
begin
insert into @tbl default values
select @count=@count-1
end
return
end
go
--获取语句
select 10000+item_id
from dbo.fnt_getallNum() a where item_id not in (select right(单号,4) from t)