如何通过SQL从两张表中找出最小的缺失数字？

问题场景 #

现有两张表：

• bidder表的bidder_number字段（对应示例的Table1 columna）包含值1,2,3,8（且auctionid=1）
• customer表的permanent_biddernumber字段（对应示例的Table2 columnb）包含值4,10,11
  需要找出最小的缺失数字，期望结果为5。

原代码仅能返回下一个可用数字（如12），核心问题是候选缺失值的范围覆盖不全——原代码只基于bidder表的相邻数值生成候选（如4、9），但未覆盖5-7这类既不在bidder也不在customer的中间区间。

修正后的SQL方案 #

方案一：生成全量候选值筛选 #

WITH AllNumbers AS (
    -- 合并两张表的所有有效正整数，去重
    SELECT bidder_number AS num 
    FROM bidder 
    WHERE auctionid = 1 AND bidder_number > 0
    UNION
    SELECT permanent_biddernumber AS num 
    FROM customer 
    WHERE permanent_biddernumber > 0 AND permanent_biddernumber IS NOT NULL
),
MissingCandidates AS (
    -- 候选缺失值：包含1（处理1缺失的情况），以及所有已有数字+1
    SELECT 1 AS candidate
    UNION ALL
    SELECT num + 1 AS candidate FROM AllNumbers
)
-- 筛选不在合并集合中的候选值，取最小的
SELECT MIN(candidate) AS lowest_missing_value
FROM MissingCandidates
WHERE candidate NOT IN (SELECT num FROM AllNumbers);

方案二：基于存在值的缺失判断 #

WITH AllNumbers AS (
    SELECT bidder_number AS num 
    FROM bidder 
    WHERE auctionid = 1 AND bidder_number > 0
    UNION
    SELECT permanent_biddernumber AS num 
    FROM customer 
    WHERE permanent_biddernumber > 0 AND permanent_biddernumber IS NOT NULL
)
SELECT COALESCE(
    -- 找第一个"存在数字+1"不在集合中的值
    (SELECT MIN(num + 1) FROM AllNumbers WHERE num + 1 NOT IN (SELECT num FROM AllNumbers)),
    -- 若所有数字连续，检查1是否缺失，否则返回最大值+1
    (SELECT CASE WHEN MIN(num) > 1 THEN 1 ELSE MAX(num) + 1 END FROM AllNumbers)
) AS lowest_missing_value;

逻辑说明 #

1. AllNumbers：将两张表的有效正整数合并去重，统一作为参考集合。
2. 候选值覆盖：通过包含1和所有存在数字+1，确保所有可能的缺失点都被纳入筛选范围，避免遗漏中间区间。
3. 最终筛选：从候选值中找出第一个不在参考集合中的数字，即为最小缺失值。

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