关于票号的处理，通过库存信息和已使用的信息，算出还可以使用的票号信息

Question

哈哈，都接分来了。这个在oracle里好弄些，因为有分析函数可以引用。说明下，我的是2000de，所以最好不要用2005的专有函数不过有2005的实现也可以分享下。这个想法是在roy_88回复 ~~~~求助查询连续记录的问题~~~~~ 想到的可以通过sql来直接实现。虽然我已经实现了，可是感觉效率不高，不知道和通过自定义函数+cursor哪个效率高？

Answer 1

不要用自定义函数啊，因为自定义函数我写完了。虽然sql的也写完了可是感觉效率不高！

Answer 2

to 7楼这个票号一般都是有为数规定的，前面可能有字轨如AB0000001，我只是把前面的字母没有放上来！

Answer 3

学习 SQL 很深啊！！！

Answer 4

小梁，苦行僧，dobear，快帮忙啊！还有其他高手，想想吧，这个sql应该很难吧！！

Answer 5

table1
序号  类别 起始号  终止号  总数  已用票号        已用票数  结余票号              结余票数 组合编号(主键)
1     A    0000001 0000005  5    0000001,0000003  2     0000002,0000004,0000005  3   
A-0000001-0000005  
2     B    0000006 00000010 5    0000006,0000008  2      0000007,0000009,0000010  3       B-0000006-0000010
table2
组合编号            类别    票号   
A-0000001-0000005   A       0000001
A-0000001-0000005   A       0000003
B-0000006-0000010   B       0000006
B-0000006-0000010   B       0000008希能在table2写触发器更新table1已用票号，已用票数,结余票号，结余票数的字段。--------------------------------------------------------------------------------------------------------------------
--示例--示例数据
create table table1(序号 int,类别 varchar(10),起始号 char(7),终止号 char(7),总数 int,已用票号 varchar(8000),已用票数 int,结余票号 varchar(8000),结余票数 int,组合编号 varchar(20))
insert table1 select 1,'A','0000001','0000005',5,'',0,'0000001,0000002,0000003,0000004,0000005',5,'A-0000001-0000005'
union  all    select 2,'B','0000006','0000010',5,'',0,'0000006,0000007,0000006,0000009,0000010',5,'B-0000006-0000010'create table table2(组合编号 varchar(20),类别 varchar(10),票号 char(7))
go--处理的触发器
create trigger tr_process on table2
for insert,update,delete
as
declare @组合编号 varchar(20)
 ,@sta varchar(8000),@nma int
 ,@stb varchar(8000),@nmb int--处理被删除的
select d.组合编号,d.票号
 ,已用票号=','+a.已用票号+',',a.已用票数
 ,结余票号=a.结余票号,a.结余票数
into #d 
from deleted d,table1 a
where d.组合编号=a.组合编号
order by d.组合编号,d.票号if @@rowcount>0
begin
 update #d set 
  @sta=replace(
   case 组合编号 when @组合编号 then @sta else 已用票号 end,
   ','+票号+',',','),
  @nma=case 组合编号 when @组合编号 then @nma-1 else 已用票数-1 end,
  @stb=case 组合编号 when @组合编号 then @stb+',' 
   else case 结余票号 when '' then '' else 结余票号+',' end end+票号,
  @nmb=case 组合编号 when @组合编号 then @nmb+1 else 结余票数+1 end,
  @组合编号=组合编号,
  已用票号=@sta,已用票数=@nma,
  结余票号=@stb,结余票数=@nmb update a set 已用票号=case b.已用票数 when 0 then ''
   else substring(b.已用票号,2,len(b.已用票号)-2) end
  ,已用票数=b.已用票数
  ,结余票号=b.结余票号
  ,结余票数=b.结余票数
 from table1 a,#d b,(
  select 组合编号,已用票数=min(已用票数)
  from #d 
  group by 组合编号
 )c where a.组合编号=b.组合编号
  and c.组合编号=b.组合编号
  and c.已用票数=b.已用票数
end--处理新增的
select i.组合编号,i.票号
 ,已用票号=a.已用票号,a.已用票数
 ,结余票号=','+a.结余票号+',',a.结余票数
into #i
from inserted i,table1 a
where i.组合编号=a.组合编号
order by i.组合编号,i.票号if @@rowcount>0
begin
 set @组合编号=null
 update #i set 
  @sta=case 组合编号 when @组合编号 then @sta+',' 
   else case 已用票号 when '' then '' else 已用票号+',' end end+票号,
  @nma=case 组合编号 when @组合编号 then @nma+1 else 已用票数+1 end,
  @stb=replace(
   case 组合编号 when @组合编号 then @stb else 结余票号 end,
   ','+票号+',',','),
  @nmb=case 组合编号 when @组合编号 then @nmb-1 else 结余票数-1 end,
  @组合编号=组合编号,
  已用票号=@sta,已用票数=@nma,
  结余票号=@stb,结余票数=@nmb update a set 已用票号=b.已用票号
  ,已用票数=b.已用票数
  ,结余票号=case b.结余票数 when 0 then ''
   else substring(b.结余票号,2,len(b.结余票号)-2) end
  ,结余票数=b.结余票数
 from table1 a,#i b,(
  select 组合编号,已用票数=max(已用票数)
  from #i
  group by 组合编号
 )c where a.组合编号=b.组合编号
  and c.组合编号=b.组合编号
  and c.已用票数=b.已用票数
end
go--插入数据测试
insert table2 select 'A-0000001-0000005','A','0000001'
union  all    select 'A-0000001-0000005','A','0000003'
union  all    select 'B-0000006-0000010','B','0000006'
union  all    select 'B-0000006-0000010','B','0000008'--删除测试
delete from table2 where 票号='0000008'--更新测试
update table2 set 票号='0000008' where 票号='0000006'
go--显示处理结果
select * from table1
go--删除测试
drop table table1,table2/*--测试结果(自己看)--*/

Answer 6

if not object_id('t1') is null
    drop table t1
Go
Create table t1([type] nvarchar(3),[s] nvarchar(5),[e] nvarchar(5))
Insert t1
select N'001',N'0001',N'0020' union all
select N'002',N'0001',N'0020' union all
select N'004',N'00001',N'00030'
Go
Select * from t1
--> --> 生成測試數據
 
if not object_id('t2') is null
    drop table t2
Go
Create table t2([type] nvarchar(3),[s] nvarchar(5),[e] nvarchar(5))
Insert t2
select N'001',N'0001',N'0005' union all
select N'001',N'0006',N'0009' union all
select N'002',N'0010',N'0012' union all
select N'002',N'0015',N'0019' union all
select N'004',N'00013',N'00014' union all
select N'004',N'00015',N'00020' union all
select N'004',N'00025',N'00027'
Go
Select * from t2--try：
declare @n int
select @n=max(cast(e as int)-cast(s as int)+1) from t1
exec('select top '+@n+' id=identity(int,1,1) into # from syscolumns
select * from 
(select type,id=right(replicate(''0'',len(s))+ltrim(id),len(s)) from t1,## where id>=cast(s as int) and id<=cast(e as int))a
where not exists(select 1 from t2 where type=a.type and cast(id as int) between s and e)')

Answer 7

上面手误，临时表多打了个#。楼主自己去掉下。。declare @n int
select @n=max(cast(e as int)-cast(s as int)+1) from t1
exec('select top '+@n+' id=identity(int,1,1) into # from syscolumns
select * from 
(select type,id=right(replicate(''0'',len(s))+ltrim(id),len(s)) from t1,# where id>=cast(s as int) and id<=cast(e as int))a
where not exists(select 1 from t2 where type=a.type and cast(id as int) between s and e)')

Answer 8

to pt我要的是号段。能否不用临时表？我要的结果是001 0010 0020
002 0001 0009
002 0013 0014
002 0020 0030
002 0041 0050
004 00001 00012
004 00021 00024
004 00028 00030

Answer 9

select *
from
(
select type,cast(s as int)+1 as s,cast(ss as int)-1 as e
from
(
select t1.type,t1.s ,min(t2.s) as ss
from t1 join t2
on t1.type = t2.type
group by t1.type,t1.s
union all
select t1.type,max(t2.e) as s,t1.e as ss
from t1 join t2
on t1.type = t2.type
group by t1.type,t1.e
union all
select a.type,a.e,min(b.s) as ss
from t2 a,t2 b
where a.type = b.type
and b.s >= a.e
group by a.type,a.e
)t
)tt
where s<=e
order by type,s

Answer 10

前一段代码忘记考虑边界问题了
以下这段可以完成BOSS的功能
但是，数据类型都被处理成了INT型
select *
from
(
select type,cast(s+1 as char) as s,cast(ss-1 as char) as e
from
(
select a.type,cast(a.e as int) as s,cast(min(b.s) as int) as ss
from t2 a,t2 b
where a.type = b.type
and b.s >= a.e
group by a.type,a.e
)t
union all
select t1.type,cast(t1.s as int) ,cast(min(t2.s) as int)-1 as ss
from t1 join t2
on t1.type = t2.type
group by t1.type,t1.s
union all
select t1.type,cast(max(t2.e) as int)+1 as s,t1.e as ss
from t1 join t2
on t1.type = t2.type
group by t1.type,t1.e)tt
where s<=e

Answer 11

select type,max(s) s,e,os,oe from (
select isnull(x.type,y.type) type,x.e s,y.s e,isnull(x.os,y.os) os,isnull(x.oe,y.oe) oe from (
select a.type,a.e,b.s os,b.e oe from t2 a,t1 b where a.type=b.type and a.s >= b.s and a.e<=b.e
and not exists (select 1 from t2 where type=a.type and s=a.e+1)) x
full join 
(select a.type,a.s,b.s os,b.e oe from t2 a,t1 b where a.type=b.type and a.s >= b.s and a.e<=b.e
and not exists (select 1 from t2 where type=a.type and e=a.s-1)
) y 
on x.type=y.type and x.os=y.os and x.oe=y.oe and y.s>=x.e ) b
group by type,os,oe,e001 0009 NULL 0001 0020
001 NULL 0001 0001 0020
002 0040 NULL 0001 0050
002 NULL 0010 0001 0050
002 0012 0015 0001 0050
002 0019 0025 0001 0050
002 0026 0031 0001 0050
004 00029 NULL 00001 00030
004 NULL 00013 00001 00030
004 00020 00025 00001 00030这个是我写的，感觉效率不高！然后再此基础上就可以算出还可以使用的票了

Answer 12

其实我的逻辑上想来满简单的
解释下数据处理模型。t1
type    s    e
A       ssa  eea
B       ssb  eebt2
type    s    e
A       s1   e1
A       s2   e2
.......
首先，ssa<=s1<=e1<=s2<=e2<=.......<=eea
区间表示就是：
ssa    s1--e1    s2--e2  .....   eea
“--”表示已经使用的区段
要得到的是“--”区段
ssa----s1  e1----s2  e2--......--eea实际上，只要把s1换到eea的位置，把s2\e1互换位置，再做相应的+-处理就可以了最终的结果表：
FT
TYPE    S           E
A       ssa         s1-1
A       e1+1        s2-1
......
A       e.last+1    eea 
 
然后根据E>=S做最后的选择
最后就事数据转换的问题