创建并填充临时表：查询特定实体编号序列缺失值的SQL问询

我来帮你完善这个SQL方案，实现创建临时表存储特定实体的编号序列，进而找出其中的缺失编号：

1. 定义实体ID并创建临时表 #

首先补全你的变量定义，创建临时表并插入目标实体的编号（注意处理字段类型的转换）：

-- 定义要查询的目标实体ID，替换成你实际需要的ID值
DECLARE @EntityId INT = 123;

-- 创建临时表，存储该实体的所有有效编号
CREATE TABLE #DocumentNumbers (DocumentNr INT);

-- 将原表中目标实体的编号插入临时表
-- 如果szDocumentNrLocal是字符串类型，先转换为整数；如果本身是整数，去掉CAST即可
INSERT INTO #DocumentNumbers (DocumentNr)
SELECT 
    CAST(szDocumentNrLocal AS INT)
FROM 
    YourOriginalTableName -- 替换成你的原表名称
WHERE 
    gbaEntityIdFk = @EntityId
    AND szDocumentNrLocal IS NOT NULL -- 过滤空值
    AND ISNUMERIC(szDocumentNrLocal) = 1; -- 确保字符串是有效数字（仅当字段是字符串时需要）

2. 生成连续序列并查找缺失编号 #

接下来我们需要生成从该实体最小编号到最大编号的连续数字序列，再和临时表中的编号对比，找出不在序列里的缺失值：

-- 获取该实体编号的范围（最小值和最大值）
DECLARE @MinNr INT, @MaxNr INT;
SELECT 
    @MinNr = MIN(DocumentNr), 
    @MaxNr = MAX(DocumentNr)
FROM #DocumentNumbers;

-- 用递归CTE生成连续数字序列
WITH NumberSequence AS (
    SELECT @MinNr AS Nr
    UNION ALL
    SELECT Nr + 1
    FROM NumberSequence
    WHERE Nr < @MaxNr
)
-- 查询缺失的编号
SELECT 
    Nr AS MissingDocumentNr
FROM 
    NumberSequence
WHERE 
    Nr NOT IN (SELECT DocumentNr FROM #DocumentNumbers)
ORDER BY 
    Nr;

-- 用完临时表后记得删除，避免占用资源
DROP TABLE #DocumentNumbers;

额外注意事项 #

• 如果szDocumentNrLocal包含非数字前缀/后缀（比如"DOC-123"），需要先提取纯数字部分，例如用SUBSTRING和PATINDEX函数处理：CAST(SUBSTRING(szDocumentNrLocal, PATINDEX('%[0-9]%', szDocumentNrLocal), LEN(szDocumentNrLocal)) AS INT)
• 如果你的数据量很大，递归CTE可能效率较低，可以提前创建一个包含大量连续数字的物理表（比如从1到1000000），用它来代替递归CTE生成序列
• 如果编号序列不是从最小值连续起始（比如存在断号在最小值之前），可以根据实际需求调整序列的起始值

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