MariaDB中如何获取两张表空闲ID区间的交集

问题背景 #

现有两张结构相同的表tableA和tableB，数据如下：

需求：对比tableA.ID和tableB.ID，找出同时在两张表中处于空闲状态的ID，并获取这些空闲ID的区间。

现有单表查询方案 #

查询单表空闲ID区间的SQL语句如下：

SELECT
    a.ID + 1 start,
    min(b.ID) - 1 end,
    min(b.ID) - a.ID - 1 gap
   
FROM
   tableA a,
   tableB b
   
WHERE      a.ID < b.ID
GROUP BY   a.ID
HAVING     start < MIN(b.ID)

该语句返回的单表空闲区间结果：

tableA的空闲区间 #

tableB的空闲区间 #

预期结果 #

需要获取两张表都空闲的ID区间，预期结果如下：

失败的尝试 #

曾尝试使用IN子查询的方式，但未成功，代码如下：

WHERE a.ID < b.ID AND a.ID IN (

SELECT
    c.ID+1 startID,
    min(d.ID) - 1 endID,
    min(d.ID) - c.ID - 1 gap
   
from
   tableB c,
   tableB d

where      c.rowid < d.rowid
)

可行解决方案 #

核心思路是先分别生成两张表的空闲区间，再计算这些区间的交集，交集部分即为两张表共同的空闲区间。

完整SQL代码 #

WITH free_a AS (
    -- 获取tableA的所有空闲区间
    SELECT
        a.ID + 1 AS start,
        MIN(b.ID) - 1 AS end,
        MIN(b.ID) - a.ID - 1 AS gap
    FROM tableA a
    JOIN tableA b ON a.ID < b.ID
    GROUP BY a.ID
    HAVING a.ID + 1 < MIN(b.ID)
),
free_b AS (
    -- 获取tableB的所有空闲区间
    SELECT
        c.ID + 1 AS start,
        MIN(d.ID) - 1 AS end,
        MIN(d.ID) - c.ID - 1 AS gap
    FROM tableB c
    JOIN tableB d ON c.ID < d.ID
    GROUP BY c.ID
    HAVING c.ID + 1 < MIN(d.ID)
)
-- 计算两个空闲区间集合的交集
SELECT
    GREATEST(fa.start, fb.start) AS start,
    LEAST(fa.end, fb.end) AS end,
    LEAST(fa.end, fb.end) - GREATEST(fa.start, fb.start) + 1 AS gap
FROM free_a fa
JOIN free_b fb ON fa.start <= fb.end AND fb.start <= fa.end
WHERE GREATEST(fa.start, fb.start) <= LEAST(fa.end, fb.end)
ORDER BY start;

逻辑说明 #

1. 生成单表空闲区间：通过CTEfree_a和free_b分别生成两张表的空闲区间，将原隐式连接改为显式JOIN，语法更规范。
2. 计算区间交集：通过JOIN条件筛选出两个区间存在重叠的记录，用GREATEST取两个区间起始的最大值作为交集起始，LEAST取两个区间结束的最小值作为交集结束，最终计算出交集的长度（gap）。
3. 过滤无效区间：通过WHERE条件确保起始不大于结束，避免出现无效区间。

补充说明（可选） #

如果需要包含最大ID之后的连续空闲区间（例如tableA中10之后的所有ID），可以在CTE中补充如下逻辑：

-- 修改free_a，补充最大ID之后的区间
free_a AS (
    SELECT
        a.ID + 1 AS start,
        MIN(b.ID) - 1 AS end,
        MIN(b.ID) - a.ID - 1 AS gap
    FROM tableA a
    JOIN tableA b ON a.ID < b.ID
    GROUP BY a.ID
    HAVING a.ID + 1 < MIN(b.ID)
    UNION ALL
    SELECT MAX(ID) + 1 AS start, NULL AS end, NULL AS gap
    FROM tableA
    WHERE EXISTS (SELECT 1 FROM tableA)
),
-- free_b同理

内容的提问来源于stack exchange，提问作者nopinopa