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),