如何丢失文件编号？ [英] How to get missing document number?

问题描述

HeaderId DocumentNo
-------- ----------
26953	 PMRS132833
26951	 PMRS132830
26950	 PMRS132825
26949	 PMRS132824
26948	 PMRS132822
26945	 PMRS132821
26944	 PMRS132820

这里headerId在连续的顺序但文件没有错过由于某种原因，所以我想要一个查询，将给文件没有丢失。



＃我希望输出为

o / p： -

PMRS132823

PMRS132826

PMRS132827

PMRS132828

PMRS132829

PMRS132831

PMRS132832



我也试过谷歌，但也许我不幸运。请帮忙。

Here headerId in Continious order but Document No missed because of some reason, so i want a query which will give the document no which is missing.

#I want output as
o/p:-
PMRS132823
PMRS132826
PMRS132827
PMRS132828
PMRS132829
PMRS132831
PMRS132832

I have tried google as well, but perhaps i was not lucky.Please help.

推荐答案

试试这个：

解决方案1 ​​

Try this:
Solution 1

DECLARE @tmp TABLE (HeaderId INT, DocumentNo VARCHAR(30))

INSERT INTO @tmp (HeaderId, DocumentNo)
SELECT 26953, 'PMRS132833'
UNION ALL SELECT 26951, 'PMRS132830'
UNION ALL SELECT 26950, 'PMRS132825'
UNION ALL SELECT 26949, 'PMRS132824'
UNION ALL SELECT 26948, 'PMRS132822'
UNION ALL SELECT 26945, 'PMRS132821'
UNION ALL SELECT 26944, 'PMRS132820'


--SELECT HeaderId, DocumentNo, CONVERT(INT, RIGHT(DocumentNo, LEN(DocumentNo)-LEN('PMRS'))) AS DocNo
--FROM @tmp
--ORDER BY DocNo

DECLARE @MinNo INT
DECLARE @MaxNo INT

SELECT @MinNo = MIN(CONVERT(INT, RIGHT(DocumentNo, LEN(DocumentNo)-LEN('PMRS')))) 
FROM @tmp

SELECT @MaxNo = MAX(CONVERT(INT, RIGHT(DocumentNo, LEN(DocumentNo)-LEN('PMRS')))) 
FROM @tmp

;WITH Numbers AS
(
        --initial part
	SELECT @MinNo AS CurNo, @MinNo AS MinNo, @MaxNo AS MaxNo
	UNION ALL
        --recursive part
	SELECT CurNo +1 AS CurNo, MinNo, MaxNo
	FROM Numbers
	WHERE CurNo +1 <@MaxNo
)
SELECT MissingDocNo
FROM (
	SELECT 'PMRS' + CONVERT(VARCHAR(10),CurNo) AS MissingDocNo
	FROM Numbers
	) AS T
WHERE MissingDocNo NOT IN (SELECT DocumentNo AS MissingDocNo FROM @tmp)
ORDER BY MissingDocNo

In above example i use <a href="http://technet.microsoft.com/en-us/library/ms175972.aspx">CTE</a> to "produce" ;) missing values.

Solution 2
Another way is to use <a href="http://technet.microsoft.com/en-US/library/ms178642.aspx">WHILE</a>[<a href="http://technet.microsoft.com/en-US/library/ms178642.aspx" target="_blank" title="New Window">^</a>] loop:
<pre lang="sql">DECLARE @MisVal TABLE (DocumentNo VARCHAR(30))

WHILE (@MinNo<@MaxNo)
BEGIN
    IF NOT EXISTS(SELECT DocumentNo FROM @tmp WHERE DocumentNo='PMRS' + CONVERT(VARCHAR(30),@MinNo))
        INSERT INTO @MisVal(DocumentNo) VALUES('PMRS' + CONVERT(VARCHAR(30),@MinNo))
    SET @MinNo = @MinNo+1
END

SELECT *
FROM @MisVal

这并不简单，原因有两个：

1）首先，您检查的字段不是数字 - 它是alpha，因此紧跟在'PMRS132822'之后的值不是'PMRS132823 ' - 'PMRS132822A'就在他们之间。

所以你需要做的第一件事就是首先投射并比较字符串的整数部分。

2缺少的条目实际上并不存在，所以很难找到它们！ ：笑：

当使用整数时，这将起作用（在一定程度上）：

This is not simple, for two reasons:
1) Firstly, the field you are checking is no numeric - it is alpha, so the value following immediately after 'PMRS132822' is not 'PMRS132823' - 'PMRS132822A' is between them for example.
So the first thing you need to do is cast and compare the "integer" part of the string first.
2) Missing entries don't actually exist, so it's really difficult to find them! :laugh:
When using integers, this will work (to an extent):

SELECT  DocumentNo+1 FROM MyTable t
WHERE NOT EXISTS ( SELECT 0 FROM MyTable t2 WHERE t2.DocumentNo = t.DocumentNo+1 )
ORDER BY DocumentNo

但是 - 它很大但是 - 它会错过数字如果132823和132824都丢失了。



另一种想到的方法是创建一个临时表，并用最小值和最小值之间的所有可能值填充它。最大值，并在两个表之间进行LEFT OUTER连接。

But - and it's a big but - it will miss numbers if 132823 and 132824 are both missing.

The other way that springs to mind is to create a temporary table and populate it with all possible values between the minimum and the maximum, and do a LEFT OUTER join between the two tables.

这篇关于如何丢失文件编号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！