declare @t table(row int identity primary key,f int,t int)
declare @s table(row int identity primary key,code char(8))
insert @s (code) from select 你的票号 from 你的数据表 order by 票据使用顺序
declare @p int,@last int,@this int
set @p=1
set @last=0
while @p<=(select max(row) from @s)
begin
select @ this=cast((select code from @s where row=@p) as int)
if @this<>@last+1
insert @t(f,t) values (@this,@this)
else
update @t set t=@this where row=(select max(row) from @t)
set @last=@this
set @p=@p+1
end
select f,t from @t order by row
declare @s table(row int identity primary key,code char(8))
insert @s (code) from select 你的票号 from 你的数据表 order by 票据使用顺序
declare @p int,@last int,@this int
set @p=1
set @last=0
while @p<=(select max(row) from @s)
begin
select @ this=cast((select code from @s where row=@p) as int)
if @this<>@last+1
insert @t(f,t) values (@this,@this)
else
update @t set t=@this where row=(select max(row) from @t)
set @last=@this
set @p=@p+1
end
select f,t from @t order by row
insert into @ values('20--50')
insert into @ values('60--90')
insert into @ values('152--212')select identity(int,1,1) as id,* ,
cast(right(票号,len(票号)-charindex('--',票号)-1 ) as int)-cast(left(票号,charindex('--',票号)-1 ) as int) as 数量
into #temp from @
select a.票号 from #temp a
where a.id<=
(select max(id)+1 from
(
select sum(b.数量) as 数量,a.id
from #temp a ,#temp b
where a.id>=b.id
group by a.id
)b
where 数量<40) ----这里放发的条数
drop table #temp
insert into t values('20--50')
insert into t values('60--90')
insert into t values('152--212')
go
create proc getit(@number int)
as
select identity(int,1,1) as id,* ,
cast(right(票号,len(票号)-charindex('--',票号)-1 ) as int)-cast(left(票号,charindex('--',票号)-1 ) as int) as 数量
into #temp from t
select a.票号 from #temp a
where a.id<=
(select max(id)+1 from
(
select sum(b.数量) as 数量,a.id
from #temp a ,#temp b
where a.id>=b.id
group by a.id
)b
where 数量<@number)
drop table #temp
go
调用:getit 20 getit 40