CDと本の管理システムをExcelで作成する(7.検索結果(配列)をSQLで並び替え)

前回でレーベンシュタイン距離を付与することが出来ました

www.excellovers.com

今回はレーベンシュタイン距離の値に応じ検索結果を並び替えたいと思います。

ソートアルゴリズム？いえSQLです

検索結果はユーザーフォームに表示される直前に多段階配列(ジャグ配列)に取り込まれています。

であるならば通常はバブルソートなどのソートアルゴリズムを使用して並び替えるところですが、今回はちょっと違う方法で並び替えたいと思います。

配列をIn Memory Databaseに取り込んでSQLで並び替え

これです。

無理やりです。

使ってみたいんです、In Memory Database。

では早速やってみます

前回コード

Private Sub CommandButton1_Click()
     Dim myDBHandle As Long
     
     SQLite3Initialize
    SQLite3OpenV2 "C:\XXX\CDBookManager.db3", myDBHandle, _
          SQLITE_OPEN_READONLY, ""
     
     Dim mySQL As String
     Dim myStmtHandle As Long
     
     mySQL = "select タイトル from CDBookManager"
     SQLite3PrepareV2 myDBHandle, mySQL, myStmtHandle
     
     Dim ReturnValue As Variant
     Dim ColCount As Long
     Dim i As Long
     Dim n As Long
     Dim myArray() As Variant
     Dim myVar As Variant
     Dim colType As Long
     Dim colValue As Variant
     
     ReturnValue = SQLite3Step(myStmtHandle)
     ColCount = SQLite3ColumnCount(myStmtHandle)
     ReDim myArray(ColCount)
     ReDim myVar(n)
     
     Do Until ReturnValue = SQLITE_DONE
          For i = 0 To ColCount - 1
               colType = SQLite3ColumnType(myStmtHandle, i)
               colValue = ColumnValue(myStmtHandle, i, colType)
               myArray(i) = colValue
          Next i

          myArray(ColCount) = Levenshtein3(TextBox_Title.Value, myArray(0))
          myVar(n) = myArray
          n = n + 1
          ReDim Preserve myVar(n)
          ReturnValue = SQLite3Step(myStmtHandle)
     Loop
     ReDim Preserve myVar(n - 1)
     SQLite3Finalize myStmtHandle
     SQLite3Close myDBHandle
     
'---ここに次に紹介するコードを挿入--------------------------------------------

     ListBox_Result.List = WorksheetFunction.Transpose(WorksheetFunction.Transpose(myVar))
        
     
End Sub

SQLでデータの並び替え

     Dim Titles() As Variant
     
'①
     SQLite3OpenV2 "Sort.db3", _
          myDBHandle, SQLITE_OPEN_READWRITE + SQLITE_OPEN_MEMORY, ""
     SQLite3PrepareV2 myDBHandle, _
          "create table sortData(Title text,Levenshtein integer)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     
     Titles = WorksheetFunction.Transpose(WorksheetFunction.Transpose(myVar))
'②
     For i = LBound(Titles) To UBound(Titles)
          mySQL = "insert into sortData values(""" & Titles(i, 1) & """," & Titles(i, 2) & ")"
          SQLite3PrepareV2 myDBHandle, mySQL, myStmtHandle
          SQLite3Step myStmtHandle
          SQLite3Finalize myStmtHandle
     Next i
     
'③
     mySQL = "select * from sortData order by levenshtein desc"
     SQLite3PrepareV2 myDBHandle, mySQL, myStmtHandle
     ColCount = SQLite3ColumnCount(myStmtHandle)
     n = 0
     ReDim myArray(ColCount - 1)
     ReDim myVar(n)
 
'④
     ReturnValue = ""
     Do Until ReturnValue = SQLITE_DONE
          For i = 0 To ColCount - 1
               colType = SQLite3ColumnType(myStmtHandle, i)
'⑤            
               If colType <> 5 Then
                    colValue = ColumnValue(myStmtHandle, i, colType)
                    myArray(i) = colValue
               End If
          Next i
'⑥
          If colType <> 5 Then
               myVar(n) = myArray
               n = n + 1
               ReDim Preserve myVar(n)
          End If
          ReturnValue = SQLite3Step(myStmtHandle)
     Loop
     ReDim Preserve myVar(n - 1)
     SQLite3Finalize myStmtHandle
     SQLite3Close myDBHandle