如何设计票据系统数据库结构?

[简单的事情]

如何设计票据系统数据库结构?

票据入库以 一段区间进行入库如(020000-030000)
票据出库也以一段区间进行

然后票据售出的时候是以单张或区间进行售票

现在要统计各个票据类型的库存区间？？？？？？？？？？？？？？？？？？

如：入库(0200000-0300000),出库(0200000-0300000),
有两个售票点各
售出:0200001,0200002
0250001,0250002

现经统计库存，结果:
0200003--0250000
0250003--0300000

[hglhyy]

不明白楼主的意思！

可否再详细点

[简单的事情]

简单的说就是票据入库的时候是以一段区间进行数据的录入，售出的时候是以单张出去的，那么如何统计现有库存数？？？？

[zjcxc]

如果查询是重要的,那么,增加一个明细表,将入库的数据分拆为单张.


如果数据处理是重要的,那么在查询的时候再份拆入库的数据为单到临时表中,然后再统计

[简单的事情]

问题是显示的数据也是以区段的，如果按照你的方法，数据量大的时候先要拆分然后再组合，这样是否对速度有影响

[子陌红尘]

--创建数据
create table ta(p1 varchar(20),p2 varchar(20))
insert into ta select '0200000','0300000'

create table tb(p varchar(20))
insert into tb select '0200001'
insert into tb select '0200002'
insert into tb select '0250001'
insert into tb select '0250002'


--执行查询
select
m.p1,p2 = min(n.p2)
from
(select p1=right('000000'+rtrim((a.p+1)),7)
from tb a
where
exists(select 1 from ta where (a.p+1) between p1 and p2)
and
not exists(select 1 from tb where p=a.p+1)
union
select b.p1
from ta b
where not exists(select 1 from tb where p = b.p1)) m,
(select p2=right('000000'+rtrim((a.p-1)),7)
from tb a
where
exists(select 1 from ta where (a.p-1) between p1 and p2)
and
not exists(select 1 from tb where p=a.p-1)
union
select b.p2
from ta b
where not exists(select 1 from tb where p = b.p2)) n
where
m.p1 <= n.p2
group by
m.p1


--输出结果
p1 p2
--------- ---------
0200000 0200000
0200003 0250000
0250003 0300000

[子陌红尘]

--创建数据
create table ta(p1 varchar(20),p2 varchar(20))
insert into ta select '0200000','0300000'

create table tb(p varchar(20))
insert into tb select '0200001'
insert into tb select '0200002'
insert into tb select '0250001'
insert into tb select '0250002'


--执行查询
select
库存 = m.p1 + case when m.p1 = min(n.p2) then '' else '--' + min(n.p2) end
from
(select p1=right('000000'+rtrim((a.p+1)),7)
from tb a
where
exists(select 1 from ta where (a.p+1) between p1 and p2)
and
not exists(select 1 from tb where p=a.p+1)
union
select b.p1
from ta b
where not exists(select 1 from tb where p = b.p1)) m,
(select p2=right('000000'+rtrim((a.p-1)),7)
from tb a
where
exists(select 1 from ta where (a.p-1) between p1 and p2)
and
not exists(select 1 from tb where p=a.p-1)
union
select b.p2
from ta b
where not exists(select 1 from tb where p = b.p2)) n
where
m.p1 <= n.p2
group by
m.p1


--输出结果
库存
----------------
0200000
0200003--0250000
0250003--0300000

[简单的事情]

如果票据加上退库操作呢(以区段进行退库)