Oracle数据库中查找门店缺失的doc_no序列问题

假设你的表名为store_docs，可以用递归CTE（通用表表达式）生成每个门店的完整doc_no序列，再和原表对比找出缺失值：

完整SQL代码 #

WITH store_doc_ranges AS (
    -- 获取每个门店的doc_no起止范围
    SELECT 
        store_code,
        MIN(doc_no) AS min_doc,
        MAX(doc_no) AS max_doc
    FROM store_docs
    GROUP BY store_code
),
number_series AS (
    -- 生成覆盖所有门店最大doc_no的连续数字序列
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1
    FROM number_series
    WHERE num < (SELECT MAX(max_doc) FROM store_doc_ranges)
),
store_full_docs AS (
    -- 为每个门店生成其范围内的所有doc_no
    SELECT 
        sdr.store_code,
        ns.num AS doc_no
    FROM store_doc_ranges sdr
    JOIN number_series ns 
        ON ns.num BETWEEN sdr.min_doc AND sdr.max_doc
)
-- 对比原表，筛选出缺失的doc_no
SELECT 
    f.store_code,
    f.doc_no AS missing_doc_no
FROM store_full_docs f
LEFT JOIN store_docs d 
    ON f.store_code = d.store_code 
    AND f.doc_no = d.doc_no
WHERE d.doc_no IS NULL
ORDER BY f.store_code, f.doc_no;

代码说明 #

• store_doc_ranges：统计每个门店doc_no的最小、最大值，确定需要检查的序列范围
• number_series：递归生成连续数字，覆盖所有门店的最大doc_no，确保序列无遗漏
• store_full_docs：为每个门店生成其起止范围内的完整doc_no序列
• 最后通过左连接原表，筛选出原表中不存在的记录，即为该门店缺失的doc_no

如果你的数据库不支持递归CTE（如老版本MySQL），可以用提前创建的数字辅助表替代递归生成序列的部分。

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