A表对比B表 找出A表中不存在B表数据的 区间
A表 B表 字段相同 都为 a,b 类型都为bigint a,b存储为连续数字的区间 ,即最小 和最大值
A表：
2 10
11 30
35 48
49 50
54 70
73 100
……递增 几十万行
B表
1 8
10 20
21 30
32 48
49 50
51 66
69 100
……递增 几十万行
问题： A表对比B表 找出A表中不存在B表数据的 区间
结果 为：
1 1
32 34
51 53
67 68
………………
测试数据：
declare @atb table(a bigint,b bigint)
insert @atb
select 2, 10
union all
select 11 ,30
union all
select 35 ,48
union all
select 49 ,50
union all
select 54 ,70
union all
select 73, 100
select * from @atb
declare @btb table(a bigint,b bigint)
insert @btb
select 1, 8
union all
select 10, 20
union all
select 21 ,30
union all
select 32, 48
union all
select 49 ,50
union all
select 51, 66
union all
select 69 ,100
select * from @btb
declare @atb table(a bigint,b bigint)
insert @atb
select 2, 10
union all
select 11 ,30
union all
select 35 ,48
union all
select 49 ,50
union all
select 54 ,70
union all
select 73, 100
declare @btb table(a bigint,b bigint)
insert @btb
select 1, 8
union all
select 10, 20
union all
select 21 ,30
union all
select 32, 48
union all
select 49 ,50
union all
select 51, 66
union all
select 69 ,100
--SQL:
;WITH
cte1 AS(SELECT num = 1 UNION ALL SELECT 1),
cte2 AS(SELECT num = 1 FROM cte1 a, cte1 b),
cte3 AS(SELECT num = 1 FROM cte2 a, cte2 b),
cte4 AS(SELECT num = 1 FROM cte3 a, cte3 b),
cte5 AS(SELECT num = 1 FROM cte4 a, cte4 b),
cteNum as(SELECT num = ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM cte5)
SELECT beginnum=MIN(num), endnum=MAX(num) FROM
(
SELECT
b.num,
grp = b.num - ROW_NUMBER() OVER(ORDER BY b.num)
FROM @btb a
INNER JOIN cteNum B
ON b.num BETWEEN a.a AND a.b
WHERE NOT EXISTS
(
SELECT 1
FROM @atb M
WHERE b.num BETWEEN m.a AND m.b
)
) g
GROUP BY grp
/*
beginnumendnum
11
3234
5153
7172
*/
