确定一个范围是否完全被一组范​​围覆盖

如何检查范围是否为完全覆盖由一组范围。在以下示例中：

WITH ranges(id, a, b) AS (
    SELECT 1,  0, 40 UNION
    SELECT 2, 40, 60 UNION
    SELECT 3, 80, 100 UNION
    SELECT 4, 10, 30
), tests(id, a, b) AS (
    SELECT 1, 10, 90 UNION
    SELECT 2, 10, 60
)
SELECT *
FROM tests
WHERE -- ?

• 我想选择10, 60因为所有这些都被覆盖了0, 40 and 40, 60 (and 10, 30)
• 我想排除10, 90因为它暴露在60, 80

假使，假设a是包容性的并且b是排他性的，即值40属于[40, 60)并不是[0, 40)。范围可以包含间隙和各种重叠。

实际问题涉及日期+时间数据，但日期只是数字。我正在使用 SQL Server，但首选通用解决方案。

这是类似于 Thorsten 的递归解决方案。只是提供另一个例子。

WITH ranges(id, a, b) AS (
    SELECT 1,  0, 40 UNION
    SELECT 2, 40, 60 UNION
    SELECT 3, 80, 100 UNION
    SELECT 4, 10, 30 
), tests(id, a, b) AS
(   
        SELECT 1 as id, 10 as a, 90 as b
        UNION
        SELECT 2, 10, 60
), rangeFinder(a, b, ra, rfb) AS
(
    SELECT a, b, 0 AS ra, 0 AS rfb 
    FROM ranges AS r
    UNION ALL
    SELECT rangeFinder.a, ranges.b, ranges.a, rangeFinder.b 
    FROM ranges 
    JOIN rangeFinder
        ON ranges.b > rangeFinder.b
        AND ranges.a <=rangeFinder.b
), islands(a, b) AS
(
    SELECT a, b 
    FROM rangeFinder
    WHERE a NOT IN (SELECT ra FROM rangeFinder)
        AND b NOT IN (SELECT rfb FROM rangeFinder)
)
SELECT t.id, t.a, t.b FROM 
tests t
JOIN islands i
ON t.a >= i.a
AND t.b <= i.b

演示在这里：http://rextester.com/HDQ52126 http://rextester.com/HDQ52126