各们大侠: 您们好!先提前祝您们端午节快乐! 有张表中一字段fpbh,类型是varchar,存储着发票编号!我现在想按照一下发票的号码统计显示发票的总张数:我不知道怎么判断发票号是连续的,如果当中有断开的我又要从断开的后面一位开始统计!因为客户要求出来的报表要显示每一段的最小发票号和最大发票号
如:370831-372000,372005-402001,372001至372005之间是断开的!
我的表达能力不太好,请见谅!谢谢各位了!
如:370831-372000,372005-402001,372001至372005之间是断开的!
我的表达能力不太好,请见谅!谢谢各位了!
declare @t table(code varchar(10))
insert into @t
select '0001' union all
select '0002' union all
select '0004' union all
select '0005' union all
select '0009' union all
select '0010' union all
select '0011' union all
select '0012' union all
select '0015' union all
select '0016' union all
select '0019' union all
select '0030'select
a.code as code1,min(b.code) as code2,(cast(min(b.code) as int)-a.code) as 差
from
@t a,@t b
where
a.code<b.code
group by
a.code
having (cast(min(b.code) as int)-a.code)>1/*
code1 code2 差
---------- ---------- -----------
0002 0004 2
0005 0009 4
0012 0015 3
0016 0019 3
0019 0030 11
*/
(@text nvarchar(50))
returns int
as
begin
declare @t1 nvarchar(5)
declare @t2 nvarchar(5)
declare @d int
declare @l int
declare @re int
set @re = 0
set @l = len(@text)
set @d = 1
while (@d < @l)
begin
set @t1 = SUBSTRING(@text,@d,5)
set @d = @d+5
if SUBSTRING(@text,@d,1) = '-'
begin
set @d = @d+1
set @t2 = SUBSTRING(@text,@d,5)
set @d = @d+6
end
else
begin
if SUBSTRING(@text,@d,1) = ',' or SUBSTRING(@text,@d,1) = ''
begin
set @t2 = @t1
set @d = @d+1
end
end
set @re = @re + (cast(@t2 as int) - cast(@t1 as int)) + 1
end
return @re
end
goselect '1',dbo.Textcheck('10001-10002,10010-10012,10013-10015')
union
select '2',dbo.Textcheck('10001-10002,10010-10012,10013,10015')
union
select '3',dbo.Textcheck('10001-10002,10010-10012,10013-10014')
union
select '4',dbo.Textcheck('10001,10002,10004,10005,10006,10007')
go
drop function dbo.Textcheck