用二分法查找第一个断码的数字-Aaron专栏

对于一系列没有重复的数字，用二分法查出最小的一个断码数字。

对于上千万条记录，几十毫秒就搞定了。

自己扩展到带前缀的编号补充断码去吧：）

Time=t*log(Nmax,2)

Time:总时间

t:一次迭代的时间

Nmax:记录最大的数字

log(Nmax,2):以2为底，Nmax的对数

'//*******************************************************************

'//函数调用

Me.txtResult = Me.txtResult & ReplenishTable("表3", "ID") & vbCrLf

'//*******************************************************************

Public Function ReplenishTable(TableName As String, FieldName As String) As Long
    Dim lngStart As Long
    Dim lngMax As Long
    On Error GoTo ErrorHandler
    If DCount(FieldName, TableName) = 0 Then
        ReplenishTable = 1
        GoTo ExitHere
    End If
    lngMax = DMax(FieldName, TableName)
    ReplenishTable = LossNumber(TableName, FieldName, 1, lngMax)
ExitHere:
    Exit Function
ErrorHandler:
    ReplenishTable = -1
    MsgBox Err.Number & Err.Description
    Resume ExitHere
End Function

Public Function LossNumber(TableName As String, _
                             FieldName As String, _
                             Optional StartNumber As Long = -1, _
                             Optional EndNumber As Long = -1, _
                             Optional LastEnd As Long = -1 _
                             ) As Long

    Dim lngCountRecords As Long
    Dim lngCalRecords As Long
    Dim lngNextStart As Long, lngNextEnd As Long, lngNextLast As Long

    If StartNumber = -1 Then StartNumber = 1

    lngCountRecords = CountRecords(TableName, FieldName, StartNumber, EndNumber)
    lngCalRecords = CalRecords(StartNumber, EndNumber)
    If lngCountRecords > 0 Then
        If lngCountRecords = lngCalRecords Then
            If LastEnd = -1 Then
                MsgBox "No Loss"
                Exit Function
            Else
                '//后半区间
                lngNextStart = EndNumber + 1
                lngNextEnd = LastEnd
                lngNextLast = LastEnd
            End If
        Else
            '//前半区间
            lngNextStart = StartNumber
            lngNextEnd = CLng((EndNumber - StartNumber) / 2) + StartNumber
            lngNextLast = EndNumber
        End If
        LossNumber = LossNumber(TableName, FieldName, lngNextStart, lngNextEnd, lngNextLast)
    Else
        LossNumber = StartNumber
    End If
End Function

Public Function CountRecords(TableName As String, FieldName As String, StartNumber As Long, EndNumber As Long) As Long
Dim lngTotalRecords As Long
CountRecords = DCount(FieldName, TableName, FieldName & " >= " & StartNumber & " AND " & FieldName & "<=" & EndNumber)
End Function

Public Function CalRecords(StartNumber As Long, EndNumber As Long) As Long
CalRecords = EndNumber - StartNumber + 1
End Function