查找给定范围内的缺失数字 [英] Finding Missing numbers in a given range

问题描述

所以我的编码有问题，并且想知道是否有人可以帮助我。

So i have a problem with my codings and was wondering if anyone can help me.

Function FindingMissingNumber() As String

    Dim intX As Integer = Nothing
    Dim intY As Integer = Nothing
    Dim strSting As String = Nothing
    Dim strSqlQUery As String = Nothing
    Dim cmdSqlCommand As Data.SqlClient.SqlCommand = Nothing
    Dim rdrDataReader As Data.SqlClient.SqlDataReader = Nothing

    ''------------------------------------------------------------------------------------------------------------------------
    ''-> Process
    ''------------------------------------------------------------------------------------------------------------------------
    strSqlQUery = "Select ExReportPolicyNo From DBReport Order by ExReportPolicyNo"
    Dim msSqlConnection As New Data.SqlClient.SqlConnection()
    ''NOTE - You may need to CHECK your connection string!!! in the line below
    msSqlConnection.ConnectionString = "Data Source=SISBSQL\SISBSQL;Initial Catalog=ExceptionReport;User ID=sa;Password=123;"
    cmdSqlCommand = New Data.SqlClient.SqlCommand(strSqlQUery, msSqlConnection)
    If cmdSqlCommand.Connection.State = Data.ConnectionState.Closed Then cmdSqlCommand.Connection.Open()
    rdrDataReader = cmdSqlCommand.ExecuteReader()
    If rdrDataReader.HasRows Then
        Do While rdrDataReader.Read()
            intX = txtRangeLeft.Text
            intY = txtRangeRight.Text
            ''intY = rdrDataReader.GetValue(rdrDataReader.GetOrdinal("ExReportPolicyNo"))

            Do While intX <> intY
                intX = intX + 1
                If intX <> intY Then
                    strSting = strSting & intX & ", "    ''if it is not, then record the non sequential number into the string
                Else
                    Exit Do
                End If
            Loop
        Loop
    End If
    If cmdSqlCommand.Connection.State = Data.ConnectionState.Open Then cmdSqlCommand.Connection.Close()
    ''return string
    Return strSting
    ''tidy up
    intX = Nothing
    intY = Nothing
    strSting = Nothing
    strSqlQUery = Nothing
    cmdSqlCommand = Nothing
    rdrDataReader = Nothing

End Function

基本上我正在使用VB.NET和MSSQL创建一个程序来查找用户设置的给定范围之间的缺失数字。程序将从表中读取并在文本框中输出。到目前为止，上述代码是我能想到的。但问题是，我输错了而不是我想要的。这是一个 图片 [ ^ ]输出。



As你可以看到程序循环多次，并输出错误的输出。输出应该只读286118,286120,286121。问题是我哪里出错了？

Basically i''m using VB.NET and MSSQL to make a program on finding missing numbers in between a given range set by the user. The program will read from the table and give the output on a textbox. And the above codes are so far what i can come up with. But the problem is, i get wrong output and not what i want. Here''s an Image[^] of the output.

As you can see the program loops it multiple times, and give out the wrong output. The output should read only "286118, 286120, 286121". Question is where did i went wrong?

推荐答案

你可以在数据库中完全使用临时表来完成这个。

You can do this entirely in the DB, with a temporary table.

create table #temp (id int)
insert into #temp values
1,2,6,41,63

-- or you can use a loop if the values are a sequence

SELECT t.id
FROM #temp t
LEFT OUTER JOIN DBReport dr on t.id = dr.ExReportPolicyNo 
WHERE dr.id IS NULL

drop table #temp

这篇关于查找给定范围内的缺失数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！