SQL Server 2008查找PROC_MN表缺失PROCESS_SEQ值求助

首先梳理下你的场景：你有一张PROC_MN表，执行以下查询获取了指定PO和DOC的工序记录：

SELECT * FROM [PROC_MN] where PO_NO='GV17885' AND DOC_NO='622843'

查询结果整理为表格如下：

从结果能明显看到PROCESS_SEQ缺失了6，但你写的查询却没有返回这个结果，问题出在JOIN条件没有过滤指定的PO和DOC。

问题原因详解 #

你的原代码中，临时表生成了从最小到最大的PROCESS_SEQ序列，但在LEFT JOIN PROC_MN时，只匹配了tempordernumber = o.[PROCESS_SEQ]，没有限制PO_NO和DOC_NO。这意味着如果表中其他订单（比如其他PO或DOC）存在PROCESS_SEQ=6的记录，JOIN会匹配到那些记录，导致o.[PROCESS_SEQ]不为NULL，最终WHERE条件o.[PROCESS_SEQ] IS NULL无法筛选出你要找的缺失值。

修正方案一：修改原有代码 #

只需要在LEFT JOIN的关联条件中加上PO_NO和DOC_NO的过滤，确保只关联当前订单的记录：

SET NOCOUNT ON;
DECLARE @Min BIGINT
DECLARE @Max BIGINT
DECLARE @i BIGINT

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL BEGIN
DROP TABLE #TempTable
END

CREATE TABLE #TempTable (
TempOrderNumber BIGINT
)
SELECT @Min = ( SELECT MIN([PROCESS_SEQ]) FROM [PROC_MN] WITH ( NOLOCK ) WHERE PO_NO='GV17885' AND [DOC_NO]='622843')
SELECT @Max = ( SELECT MAX([PROCESS_SEQ]) FROM [PROC_MN] WITH ( NOLOCK ) WHERE PO_NO='GV17885' AND [DOC_NO]='622843')
SELECT @i = @Min
WHILE @i <= @Max
BEGIN
INSERT INTO #TempTable SELECT @i
SELECT @i = @i + 1
END
SELECT TempOrderNumber AS Missing_PROCESS_SEQ
FROM #TempTable
LEFT JOIN [PROC_MN] o WITH ( NOLOCK ) 
  ON tempordernumber = o.[PROCESS_SEQ]
  AND o.PO_NO='GV17885'  -- 新增：限制当前PO
  AND o.[DOC_NO]='622843' -- 新增：限制当前DOC
WHERE o.[PROCESS_SEQ] IS NULL

修正方案二：更高效的CTE方法（推荐） #

循环生成临时表的效率较低，推荐使用递归CTE生成连续序列，代码更简洁且性能更好：

SET NOCOUNT ON;

DECLARE @Min BIGINT, @Max BIGINT;
SELECT 
  @Min = MIN([PROCESS_SEQ]),
  @Max = MAX([PROCESS_SEQ])
FROM [PROC_MN] WITH (NOLOCK) 
WHERE PO_NO='GV17885' AND [DOC_NO]='622843';

-- 递归CTE生成连续数字序列
WITH SeqCTE AS (
  SELECT @Min AS Seq
  UNION ALL
  SELECT Seq + 1 FROM SeqCTE WHERE Seq < @Max
)
SELECT Seq AS Missing_PROCESS_SEQ
FROM SeqCTE
LEFT JOIN [PROC_MN] o WITH (NOLOCK)
  ON SeqCTE.Seq = o.[PROCESS_SEQ]
  AND o.PO_NO='GV17885' 
  AND o.[DOC_NO]='622843'
WHERE o.[PROCESS_SEQ] IS NULL
OPTION (MAXRECURSION 0); -- 当序列范围超过100时需要此选项

执行以上任意一种修正后的代码，就能正确返回缺失的PROCESS_SEQ=6了。

内容的提问来源于stack exchange，提问作者Cát Tường Vy