如何通过SQL查询获取指定ngesnr对应的最小可用TABGESDEF ID

背景说明 #

现有两张业务表：

• TABNUMBERRANGES：定义组织组（ngesnr）可使用的ID区间，字段包括nFrom（区间起始ID）、nto（区间结束ID）、ngesnr（组织组ID）
• TABGESDEF：存储组织ID与所属组的关联，字段包括ID（组织ID）、ngesnr（所属组ID）

需求：创建新组织时，已知目标ngesnr，需通过SQL直接查询该组下最小的未被使用的ID（必须在TABNUMBERRANGES定义的合法区间内）；若所有合法ID都已被占用，返回0。

示例：当ngesnr=1时，合法区间是1-2、5-7，已用ID为1、2、5、7，最小可用ID为6。

解决方案（支持递归CTE的数据库：MySQL 8.0+/PostgreSQL等） #

使用递归CTE生成所有合法ID，再筛选出未被使用的最小ID：

WITH RECURSIVE target_ranges AS (
    -- 筛选目标ngesnr对应的所有合法区间
    SELECT nFrom, nto FROM TABNUMBERRANGES WHERE ngesnr = 1 -- 替换为实际传入的ngesnr值
),
all_allowed_ids AS (
    -- 递归生成区间内的所有ID
    SELECT nFrom AS id FROM target_ranges
    UNION ALL
    SELECT aa.id + 1
    FROM all_allowed_ids aa
    JOIN target_ranges tr ON aa.id + 1 <= tr.nto
)
-- 找出未被使用的最小ID，无可用则返回0
SELECT COALESCE(MIN(aa.id), 0) AS min_available_id
FROM all_allowed_ids aa
LEFT JOIN TABGESDEF gd 
    ON aa.id = gd.ID 
    AND gd.ngesnr = 1 -- 关联目标组的已用ID
WHERE gd.ID IS NULL;

逻辑解释 #

1. target_ranges：先锁定目标组织组的所有合法ID区间；
2. all_allowed_ids：递归遍历每个区间，生成所有合法的ID值；
3. 最后通过左连接排除已被使用的ID，取剩余ID的最小值；若没有可用ID，用COALESCE返回0。

兼容老版本数据库（无递归CTE支持） #

如果数据库不支持递归CTE，可以借助数字辅助表（需提前创建或临时生成）来实现：

1. 提前创建数字表（示例） #

CREATE TABLE nums (num INT PRIMARY KEY);
-- 插入足够多的连续数字（比如1到10000，覆盖业务最大可能的ID范围）
INSERT INTO nums VALUES (1),(2),...,(10000);

2. 查询语句 #

SELECT COALESCE(MIN(n.num), 0) AS min_available_id
FROM nums n
-- 关联目标组的合法区间
JOIN TABNUMBERRANGES tr 
    ON n.num BETWEEN tr.nFrom AND tr.nto 
    AND tr.ngesnr = 1 -- 替换为目标ngesnr
-- 左连接已用ID，筛选未被使用的
LEFT JOIN TABGESDEF gd 
    ON n.num = gd.ID 
    AND gd.ngesnr = 1
WHERE gd.ID IS NULL;

测试验证 #

• 当ngesnr=1时，查询返回6；
• 当ngesnr=2时，合法区间是3-4，已用ID为3、4，查询返回0。

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