关于计算号码结存的问题,如下:
有2张表,t1表示期初结存,t2表示领走的号码,要计算出期末结存
为了简便起见,c1表示起始号码,c2表示终止号码。
create table t1(c1 varchar(10),c2 varchar(10));
insert into t1 values('101','200');
insert into t1 values('001','100');
create table t2(c1 varchar(10),c2 varchar(10));
insert into t2 values('010','020');
insert into t2 values('090','130');
t1记录
101 200
001 100
t2记录
010 020
090 130
这样得出的结存就是
001 009
021 089
131 200
要求,最好是不使用游标,太慢了。实在不行,使用游标也可以。
create table t1(c1 varchar(10),c2 varchar(10))
insert into t1 values('101','200')
insert into t1 values('001','100')
if object_id('t2') is not null drop table t2
create table t2(c1 varchar(10),c2 varchar(10))
insert into t2 values('010','020')
insert into t2 values('090','130')
------------------------------------------------------
if object_id('tempdb..#1') is not null drop table #1
select identity(int, 1, 1) as id, * into #1
from (select c1, 'b1' as typ from t1
union select c2, 'e1' from t1
union select c1, 'b2' from t2
union select c2, 'e2' from t2) a
order by c1
go
if object_id('tempdb..#2') is not null drop table #2
select a.c1, a.typ as typ1, b.c1 as c2, b.typ as typ2
into #2
from #1 a join #1 b on a.id = b.id - 1
--输出
select right(cast(1000 + c1 as varchar), 3) as c1, right(cast(1000 + c2 as varchar), 3) as c2
from (select c1, c2 from #2 where typ1 = 'b1' and typ2 = 'e1'
union select c1, c2 - 1 from #2 where typ1 = 'b1' and typ2 = 'b2'
union select c1 + 1, c2 from #2 where typ1 = 'e2' and typ2 = 'e1'
union select c1 + 1, c2 - 1 from #2 where typ1 = 'e2' and typ2 = 'b2') a
where c1 <= c2
/*
c1 c2
------ ------
001 009
021 089
131 200*/
drop table #1
drop table #2
------------------------------------------------------
drop table t1
drop table t2
已经解决
dulei115() 的方法很好!
结贴。