SQL - range falls into a row in a row

Question

I'm really stumped here ...

Let's say I have this “real numbers” table with columns BEGIN_NUM and END_NUM to represent a range of real numbers. The table will look something like this:

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

We are assigned the starting number and ending number of the range. I need to develop an SQL query to see if this range of numbers is valid.

A simple case: given that 2 and 8, as our beginning and the end of our range, this will pass, since it falls into the Row A.

Hard cases: (# 1), data 5 and 15, this will go away, as it relates to Row A and Row B, which are essentially extensions of each other, making one large range that spans two lines. (1-10 and 11-20 = 1-20).

(# 2), 5 25, , (# 1), , . (1-10 11-20 21-30 = 1-30)

(№ 3), 27 57, , , , Row C Row 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 

57 27 25 5,

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