excel - 索引一个库，然后在库中搜索一个单词并记录所有可能的匹配项

Question

我有一个包含大约 400,000 条记录的数据库，并且想将这些记录的索引结果搜索到另一列以检查它是否存在。即使只存在部分字符串，我也希望它记录下来。我有一个我在 Excel 中尝试过的示例。处理 100,000 条记录(4 小时)需要 4 个 2.8ghz 的核心。我认为在 MS Access 中执行此操作会更快。这可以在 MS Access 中更快地完成吗？我将代码粘贴在这里...

=INDEX($A:$A,AGGREGATE(15,7,ROW($A:$A)/(ISNUMBER(SEARCH($C2,$A:$A))),COLUMN(A:A)))

有没有办法在 MS Access 中使用 VBA 代码或查询来做到这一点。

Answer 1

是的，它可以完成，虽然我不确定它会更快。有一个库字符串表和另一个数据库字符串表。

使用查询的选项需要每个表中的唯一标识符字段。文本字符串可能用于此目的，但我推荐一个数字。如果尚未在每个表中添加一个自动编号字段。

考虑:

查询1:LibDB

SELECT [DB_ID]+[LIB_ID] AS ID, DB.WordDB, Lib.WordLib 
FROM Lib, DB
WHERE (((InStr([WordLib],[WordDB]))>0))
ORDER BY DB.WordDB, Lib.WordLib;

查询2

TRANSFORM First(LibDB.WordLib) AS FirstOfWordLib
SELECT LibDB.WordDB
FROM LibDB
GROUP BY LibDB.WordDB
PIVOT DCount("*","LibDB","WordDB='" & [WordDB] & "' AND ID<" & [ID])+1;

对于提供的样本数据，这非常快，但对于大型数据集执行速度可能会非常慢，以至于无法正常工作。

对于 VBA 方法，创建一个包含名为 WordDB、F1、F2 等字段的表 Result。包括尽可能多的 Fn 字段，最多 254 个。然后在一个通用模块中进行过程，如:

Sub StringMatch()
Dim rsDB As DAO.Recordset, rsLIB As DAO.Recordset, rsRST As DAO.Recordset, n As Integer
Set rsDB = CurrentDb.OpenRecordset("SELECT WordDB FROM DB ORDER BY WordDB")
Set rsRST = CurrentDb.OpenRecordset("SELECT * FROM Result WHERE 1=1")
n = 1
CurrentDb.Execute "DELETE FROM Result"
Do While Not rsDB.EOF
    rsRST.AddNew
    Set rsLIB = CurrentDb.OpenRecordset("SELECT WordLib FROM Lib " & _
                "WHERE InStr([WordLib],'" & rsDB!WordDB & "') > 0 ORDER BY WordLib;")
    rsRST!WordDB = rsDB!WordDB
    Do While Not rsLIB.EOF And n < 255
        rsRST("F" & n) = rsLIB!WordLib
        n = n + 1
        rsLIB.MoveNext
        If rsLIB.EOF Then
            rsRST.Update
            n = 1
        End If
    Loop
    rsLIB.Close
    rsDB.MoveNext
Loop
End Sub

无论采用哪种方法，输出都限制为 255 列。

如果您想继续使用 Excel，请打开列数据的记录集并将结果提供给单元格。不知道这是否会比您的 Excel 函数或 Access 更快。

Dim rsLIB As New ADODB.Recordset, rsDB As New ADODB.Recordset, c As Integer, r As Integer
Dim cnx As New ADODB.Connection
'setup the connection
'[HDR=Yes] means the Field names are in the first row
cnx.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
         "Data Source='" & ThisWorkbook.FullName & "';" & _
         "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
rsDB.Open "SELECT Database FROM [Sheet1$] WHERE NOT Database Is Null", _
     cnx, adOpenDynamic, adLockOptimistic
c = 3
r = 2
Do While Not rsDB.EOF
    rsLIB.Open "SELECT Library FROM [Sheet1$] " & _
               "WHERE InStr([Library],'" & rsDB!Database & "')>0 ORDER BY Library", _
          cnx, adOpenDynamic, adLockOptimistic
    Do While Not rsLIB.EOF
        Worksheets("Sheet1").Cells(r, c).Value = rsLIB!Library
        rsLIB.MoveNext
        c = c + 1
    Loop
    c = 3
    rsLIB.Close
    r = r + 1
    rsDB.MoveNext
Loop