--创建数据
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,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