22,301
社区成员




create table #tba (id int identity(1,1),type int,p1 int,p2 int)
create table #tbb (id int identity(1,1),type int,p1 int,p2 int)
insert into #tba select 1,1,10 union all select 2,2,100
insert into #tbb select 1,1,5 union all select 2,6,9 union all select 2,11,22
DECLARE @n AS BIGINT;
SET @n = 1000;
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n into #Nums FROM Nums WHERE n <= @n;
CREATE index #ix_n_Nums on #Nums(n)
GO
--DELETE from #tba
--DELETE from #tbb
select * FROM #tba
select * FROM #tbb
SELECT type, n.n
from #tba a
inner join #Nums n
on n.n BETWEEN a.p1 and a.p2
EXCEPT
SELECT type, n.n
from #tbb b
inner join #Nums n
on n.n BETWEEN b.p1 and b.p2
-------------------------只能帮到这了
type n
1 6
1 7
1 8
1 9
1 10
2 2
2 3
2 4
2 5
2 10
2 23
2 24
2 25
create table tba
(id int identity(1,1),type int,p1 int,p2 int)
create table tbb
(id int identity(1,1),type int,p1 int,p2 int)
insert into tba
select 1,1,10
union all select 2,1,100
insert into tbb
select 1,2,3
union all select 1,6,9
create proc P_tbatbb
as
begin
create table #
(
id int identity(1,1),
type int,
p1 int,
p2 int
)
insert into #
select type,p1,p2
from (
select a.id fid,b.id,a.type,a.p1,b.p1 - 1 [p2]
from tba a
join tbb b
on b.type = a.type
union all
select a.id,b.id,a.type,b.p2 + 1,a.p2
from tba a
join tbb b
on b.type = a.type
) aa
order by type,fid,id,p1,p2
insert into #
select a.type,a.p1,a.p2
from tba a
left join tbb b
on b.type = a.type
where b.id is null
order by type
declare @p1 int,
@p2 int,
@type int,
@p int
select @p1 = 0,@p2 = 0,@type = 1,@p = 0
update aa
set @p = case when @type <> type
or (@type = type and @p1 < p1) then p1
else @p1
end,@p1 = p1,@p2 = p2,@type = type,p1 = @p
from # aa
select type,p1,min(p2) [p2]
from #
where p1 <= p2
group by type,p1
order by type
end
go
P_tbatbb
--insert into tbb
--select 2,1,1
--union all select 2,6,9