SQL-一个范围是否落在连续的范围内

Question

我真的被难住了。。。

假设我有一个"有效数字"表，其中列BEGIN_NUM和END_NUM表示一系列有效数字。桌子看起来像这样：

ID BEGIN_NUM END_NUM
-- --------- -------
A       1      10         
B      11      20
C      21      30
D      55      70

我们得到一个范围的开始数和结束数。我需要开发一个SQL查询，看看这个数字范围是否都是有效的数字。

简单的情况：给定2和8作为我们范围的开始和结束，这将通过，因为它落在A行的范围内。

困难情况：(#1)给定5和15，这将通过，因为这属于行A和行B，它们本质上是彼此的延续，形成了一个跨越两行的大范围。(1-10和11-20＝1-20)。

(#2)给定5和25，这也会因为与(#1)相同的原因而通过，只是它跨越了几行而不是两行。(1-10和11-20和21-30=1-30)

(#3)给定27和57，这将失败，因为即使开始和结束数字在一个范围内，行C和行D之间也有间隙(因此这将使给定范围内的数字31-54无效)。

我真的不必用这个查询返回任何数据，只需显示该范围包含所有有效数字即可。如果这很重要，请使用Oracle。

以下是我目前所拥有的：

select count(*) 
from (select start_num, end_num
from ae_valid_vendor_nums
where '5' BETWEEN start_num AND end_num) tbl1,
(select start_num, end_num
from ae_valid_vendor_nums
where '25'  BETWEEN start_num AND end_num) tbl2
where (tbl1.end_num - tbl2.start_num = -1) 
OR (tbl1.start_num = tbl2.start_num AND tbl1.end_num = tbl2.end_num)

谢谢你的帮助！

Answer 1

有趣的小问题。这个想法是计算每组中的重叠，求和重叠，然后与实际范围进行比较。下面的查询通过一些示例实现了这一点。它的输出比你要求的要多一些，但这应该有助于你理解它是如何工作的：

with ValidNumbers as (
select 1 as begin_num, 10 as end_num from dual union all
select 11, 20 from dual union all
select 21, 30 from dual union all
select 55, 70 from dual
)
select v_begin, v_end,
sum(1 + (case when v_end >= vn.end_num then vn.end_num else v_end end) -
(case when v_begin >= vn.begin_num then v_begin else vn.begin_num end)
) as SumInRecords,
max(1 + v_end - v_begin) as TheRange,
(case when sum(1 + (case when v_end >= vn.end_num then vn.end_num else v_end end) -
(case when v_begin >= vn.begin_num then v_begin else vn.begin_num end)
) =
max(1 + v_end - v_begin)
then 'All' else 'Missing'
end)
from ValidNumbers vn cross join
(select 2 as v_begin, 8 as v_end from dual union all
select 2, 18 from dual union all
select 2, 28 from dual union all
select 2, 38 from dual
) const
where v_begin <= vn.end_num and
v_end >= vn.begin_num
group by v_begin, v_end;

Answer 2

一种方法是使用计数表，然后将计数与两个端点的差值进行比较(加一，因为它是包容性的)

例如

WITH validnumbers 
AS (SELECT 1  AS begin_num, 10 AS end_num FROM   dual 
UNION ALL SELECT 11, 20          FROM   dual 
UNION ALL SELECT 21, 30          FROM   dual 
UNION ALL SELECT 55, 70          FROM   dual), 
tally 
AS (SELECT LEVEL num 
FROM   dual 
CONNECT BY LEVEL <= 100 
ORDER  BY LEVEL), 
test 
AS (SELECT t.num 
FROM   validnumbers v 
inner join tally t 
ON v.begin_num <= t.num 
AND v.end_num >= t.num 
WHERE  t.num >= 27 
AND t.num <= 57) 
SELECT Count(num), 
(57 - 27) + 1
FROM   test

产生正确的失败案例

COUNT(NUM)  (57-27)+1
---------- ----------
7         31 

演示

用25和5代替57和27给我们一个正确的通过

COUNT(NUM)   (25-5)+1
---------- ----------
21         21 

演示