SQL Server 2000按分组查询缺失的连续Sale_id实现方法

首先注意：你当前使用的CTE递归语法为SQL Server 2005及以上版本支持，SQL Server 2000（版本80）无法兼容，以下方案采用数字辅助表实现，兼容2000版本，同时支持按Facility_no/Seller_id/Purchase_Date三个维度分组查询缺失值。

步骤1：创建永久数字辅助表（仅需执行一次，后续可重复使用） #

该表存储连续正整数，上限需覆盖你业务中可能出现的最大Sale_id值，以下示例生成1到100万的连续数字：

CREATE TABLE Numbers (Number INT PRIMARY KEY)
GO

DECLARE @i INT
SET @i = 1
WHILE @i <= 1000000
BEGIN
    INSERT INTO Numbers VALUES (@i)
    SET @i = @i + 1
END
GO

步骤2：分组查询缺失Sale_id代码 #

SELECT 
    t_group.Facility_no,
    t_group.Seller_id,
    t_group.Purchase_Date,
    n.Number AS Missing_Sale_id
FROM 
    -- 先获取每个分组的Sale_id取值区间
    (
        SELECT 
            Facility_no,
            Seller_id,
            Purchase_Date,
            MIN(Sale_id) AS min_sale,
            MAX(Sale_id) AS max_sale
        FROM #table
        GROUP BY Facility_no, Seller_id, Purchase_Date
    ) t_group
-- 关联数字表，拿到每个分组区间内所有应该存在的Sale_id
INNER JOIN Numbers n 
    ON n.Number BETWEEN t_group.min_sale AND t_group.max_sale
-- 左关联原表，找不到匹配的即为缺失值
LEFT JOIN #table t 
    ON t.Facility_no = t_group.Facility_no
    AND t.Seller_id = t_group.Seller_id
    AND t.Purchase_Date = t_group.Purchase_Date
    AND t.Sale_id = n.Number
WHERE t.Sale_id IS NULL
ORDER BY 
    t_group.Facility_no,
    t_group.Seller_id,
    t_group.Purchase_Date,
    n.Number

说明 #

• 如果业务中每个分组的Sale_id起始值固定为1，可将t_group.min_sale直接替换为1即可
• 数字表的上限可根据实际业务调整，只要超过所有分组的最大Sale_id即可，提前建好的静态数字表查询效率远高于动态生成序列的方案，适合SQL Server 2000的运行环境

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