访问VBA SQL，如果条件在子查询，效率和改进

Question

我已经设法使我的代码正常工作，但执行起来需要很长时间。在执行最后的SQL语句和将行写入表之前，我使用一系列打开的和关闭的记录集语句来获取我要查找的addr_ImportID。

是否有更好的方法来实现我试图使用一个更好的子查询，case语句，如果条件或其他？我对子查询的使用减慢了速度，因此，在不打开和关闭记录集的情况下操作数据可能会更有效。还是更好的select语句系列？

以下是表的外观示例：

CnBio_ID | Addr_Type | Addr_ImportID | CnAdrAll_1_IDKEY | CnAdrAll_1_DateLastChanged
0001009  | Previous  | 12344         | 1                | 03/15/2014
0001009  | Home      | H2345         | 3                | 01/02/2017
0001009  | Home      | A2344         | 2                | 01/02/2017
1223144  | Previous  | 23441         | 7                | 05/23/2010
1223144  | Other     | 31333         | 6                | 08/21/2016
1223144  | Previous  | 45848         | 5                | 08/21/2016

下面的片段是我代码的一部分。

strSql2 = "SELECT Addr_ImportID From [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' " & _
        "AND CnAdrAll_1_DateLastChanged IN (SELECT Max(CnAdrAll_1_DateLastChanged) FROM [Non-Preferred Home Addresses] " & _
        "Where CnBio_ID = '" & rs(0) & "' AND Addr_ImportID IN (SELECT Addr_ImportID From [Non-Preferred Home Addresses] " & _
        "WHERE CnBio_ID = '" & rs(0) & "' AND Addr_Type = 'Home'));"
        Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
        If Not (rsMaxAddressImportID.EOF) Then
            'Condition where more than one Home address exists updated on the same date
            If (rsMaxAddressImportID.RecordCount > 1) Then
                rsMaxAddressImportID.Close
                strSql2 = "SELECT Addr_ImportID FROM [Non-Preferred Home Addresses] WHERE CnAdrAll_1_IDKEY = " & _
                "(SELECT Max(CnAdrAll_1_IDKEY) FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' AND Addr_Type = 'Home');"
                Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
                MsgBox "Row = " & i
            End If

• 对于每个CnBio_ID
• 如果存在家庭地址，则选择Addr_ImportID of row with Max DateLastChanged
  • 如果多个主地址在同一日期上最后更新，则选择Addr_ImportID of row with Max CnAdrAll_1_IDKEY

​

• 如果没有家庭地址，则选择Addr_ImportID of row with Max DateLastChanged
  • 如果多个地址上次更新的日期相同，则选择Addr_ImportID of row with max CnAdrAll_1_IDKEY

​

在问题后由Parfait更新

'Select Distinct CIDs to loop through
'strSql = "SELECT CnBio_ID FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & ConstID & "'Group By CnBio_ID;"
strSql = "SELECT CnBio_ID FROM [Non-Preferred Home Addresses] Group By CnBio_ID;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)

If Not (rs.EOF And rs.BOF) Then
    'MsgBox rs.RecordCount

    'This section will loop through all distinct CIDs.
    i = 1
    Do Until rs.EOF

        'Select Non-Preferred Home Address with latest date updated
        strSql2 = "SELECT Addr_ImportID From [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' " & _
        "AND CnAdrAll_1_DateLastChanged IN (SELECT Max(CnAdrAll_1_DateLastChanged) FROM [Non-Preferred Home Addresses] " & _
        "Where CnBio_ID = '" & rs(0) & "' AND Addr_ImportID IN (SELECT Addr_ImportID From [Non-Preferred Home Addresses] " & _
        "WHERE CnBio_ID = '" & rs(0) & "' AND Addr_Type = 'Home'));"
        Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
        If Not (rsMaxAddressImportID.EOF) Then
            'Condition where more than one Home address exists updated on the same date
            If (rsMaxAddressImportID.RecordCount > 1) Then
                rsMaxAddressImportID.Close
                strSql2 = "SELECT Addr_ImportID FROM [Non-Preferred Home Addresses] WHERE CnAdrAll_1_IDKEY = " & _
                "(SELECT Max(CnAdrAll_1_IDKEY) FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' AND Addr_Type = 'Home');"
                Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
                MsgBox "Row = " & i
            End If

            'rsMaxAddressImportID.Close
        Else
            'Condition if all non-preferred addresses are not Home
            rsMaxAddressImportID.Close

            'Select max date last updated for next select statement
            maxDateSql = "SELECT Max(CnAdrAll_1_DateLastChanged) " & _
            "FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "';"
            Set rs2 = DBEngine(0)(0).OpenRecordset(maxDateSql, dbOpenDynaset)

            strSql2 = "SELECT Addr_ImportID FROM [Non-Preferred Home Addresses] " & _
            "WHERE [Non-Preferred Home Addresses].CnBio_ID = '" & rs(0) & "' " & _
            "AND [Non-Preferred Home Addresses].CnAdrAll_1_DateLastChanged = #" & rs2(0) & "#;"
            Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
            'MsgBox rsMaxAddressImportID(0)
            If Not (rsMaxAddressImportID.EOF) Then
                'MsgBox rsMaxAddressImportID.RecordCount
                If (rsMaxAddressImportID.RecordCount > 1) Then
                    rsMaxAddressImportID.Close
                    strSql2 = "SELECT Addr_ImportID FROM [Non-Preferred Home Addresses] WHERE CnAdrAll_1_IDKEY = " & _
                    "(SELECT Max(CnAdrAll_1_IDKEY) FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' AND CnAdrAll_1_DateLastChanged = #" & rs2(0) & "#);"
                    Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
                    'MsgBox rsMaxAddressImportID(0)
                End If

            End If

        End If

        'Get Actual address line
        'DoCmd.RunSQL "SELECT * INTO " & _
        "[Tbl_Max_Non-Preferred_Address] " & _
        "FROM [Non-Preferred Home Addresses] WHERE [Non-Preferred Home Addresses].Addr_ImportID = '" & rsMaxAddressImportID(0) & "';"

        DoCmd.RunSQL "INSERT INTO [Tbl_Max_Non-Preferred_Address] SELECT * FROM [Non-Preferred Home Addresses] WHERE [Non-Preferred Home Addresses].Addr_ImportID = '" & rsMaxAddressImportID(0) & "';"

        rsMaxAddressImportID.Close
        rs.MoveNext
        i = i + 1

    Loop
Else
    MsgBox "There are no records in the recordset."
End If

rs.Close

• “家”地址的意义在于，有些选民没有“家”地址(见CnBio_ID 1223144)。
• rs是一个选择唯一的CnBio_IDs循环通过。

用Thomas：解决方案后的更新

总的来说，一切似乎都取得了很好的结果，但我认为有一个问题的排序/顺序，我似乎不能得到正确的语法测试。我一直有语法错误。我不太熟悉圆括号等的存取解释，使它正确。我试着运行以下内容：

strSql_1 = "INSERT INTO [Table1] SELECT 1 AS priority, T1.CnBio_ID, T2.Max_Date, MAX(T1.Addr_IDKEY) AS MAX_IDKey " & _
        "FROM [TB - Non-Preferred Addresses] T1 " & _
        "INNER JOIN " & _
        "(" & _
            "SELECT CnBio_ID , MAX(CnAdrAll_1_DateLastChanged) AS Max_Date " & _
            "FROM [TB - Non-Preferred Addresses] " & _
            "WHERE (Addr_Type = 'Home' AND CnBio_ID = '0106228') " & _
            "GROUP BY CnBio_ID " & _
        ") T2 ON T1.CnBio_ID = T2.CnBio_ID AND T1.CnAdrAll_1_DateLastChanged = T2.Max_Date " & _
        "WHERE Addr_Type = 'Home' " & _
        "GROUP BY T1.CnBio_ID, T2.Max_Date "
strSql_2 = " UNION ALL SELECT 2 AS priority, T1.CnBio_ID, T2.Max_Date, MAX(T1.Addr_IDKEY) AS MAX_IDKey " & _
        "FROM [TB - Non-Preferred Addresses] T1 " & _
        "INNER JOIN " & _
        "(" & _
            "SELECT CnBio_ID , MAX(CnAdrAll_1_DateLastChanged) AS Max_Date " & _
            "FROM [TB - Non-Preferred Addresses] " & _
            "WHERE (Addr_Type IN ('Campus','Harris Home', 'NCOA2003','Other','Previous','Previous Home','Previous Student','Seasonal','Student') AND CnBio_ID = '0106228') " & _
            "GROUP BY CnBio_ID " & _
        ") T2 ON T1.CnBio_ID = T2.CnBio_ID AND T1.CnAdrAll_1_DateLastChanged = T2.Max_Date " & _
        "WHERE (Addr_Type IN ('Campus','Harris Home', 'NCOA2003','Other','Previous','Previous Home','Previous Student','Seasonal','Student') AND T1.CnBio_ID = '0106228') " & _
        "GROUP BY T1.CnBio_ID, T2.Max_Date " & _
        "ORDER BY CnBio_ID, priority"

Answer 1

这应该在纯SQL中起作用。

SELECT V.CnBio_ID, FIRST(T.Addr_ImportID) AS Addr_ImportID , FIRST(V.Max_Date) AS CnAdrAll_1_DateLastChanged, FIRST(V.MAX_IDKey) AS CnAdrAll_1_IDKEY 
FROM [Non-Preferred Home Addresses] T
INNER JOIN
(

    SELECT 1 AS priority,  T1.CnBio_ID, T2.Max_Date, MAX(T1.CnAdrAll_1_IDKEY) AS MAX_IDKey
    FROM [Non-Preferred Home Addresses] T1
    INNER JOIN
    (
        SELECT CnBio_ID , MAX(CnAdrAll_1_DateLastChanged) AS Max_Date
        FROM [Non-Preferred Home Addresses]
        WHERE Addr_Type = 'Home'
        GROUP BY CnBio_ID
    ) T2 ON T1.CnBio_ID=T2.CnBio_ID AND T1.CnAdrAll_1_DateLastChanged=T2.Max_Date
    WHERE Addr_Type = 'Home'
    GROUP BY T1.CnBio_ID, T2.Max_Date

    UNION ALL

    SELECT 2 AS priority, T1.CnBio_ID, T2.Max_Date, MAX(T1.CnAdrAll_1_IDKEY) AS MAX_IDKey
    FROM [Non-Preferred Home Addresses] T1
    INNER JOIN
    (
        SELECT CnBio_ID , MAX(CnAdrAll_1_DateLastChanged) AS Max_Date
        FROM [Non-Preferred Home Addresses]
        WHERE Addr_Type <> 'Home'
        GROUP BY CnBio_ID
    ) T2 ON T1.CnBio_ID=T2.CnBio_ID AND T1.CnAdrAll_1_DateLastChanged=T2.Max_Date
    WHERE Addr_Type <> 'Home'
    GROUP BY T1.CnBio_ID, T2.Max_Date

    ORDER BY CnBio_ID, priority

) V 
ON T.CnBio_ID = V.CnBio_ID AND T.CnAdrAll_1_DateLastChanged=V.Max_Date AND T.CnAdrAll_1_IDKEY = V.MAX_IDKey
GROUP BY V.CnBio_ID

对于表中的每个CnBio_ID，它将保持记录与日期逻辑相匹配。您可以使用它生成视图(查询)，然后使用CnBio_ID=anything查询它，以检索任何CnBio_ID的信息。

但是，我不知道性能是否可以接受，主要取决于表中的记录数量。

您应该在以下方面有索引：

• CnBio_ID
• Addr_Type
• CnAdrAll_1_IDKEY
• CnAdrAll_1_DateLastChanged

如果您有数量有限的类型不是"Home"，那么从perf角度o视图来看，将所有类型显式命名而不是搜索"<> 'HOME'“要好得多，因此您必须更改以下内容：

WHERE Addr_Type <> 'Home'

到这个

WHERE Addr_Type IN ('Previous','Other')

(2次)

如果你真的不明白发生了什么，如果你想要解释的话，请告诉我。我不想无缘无故地长篇大论。从分解和运行所有子查询开始，您应该更好地理解它。

更新

要在代码中使用我的查询，不要在VBA中硬核它！使用查询设计器对其进行查询(在标准SQL中，这称为视图)。MS Access 2016中的查询设计器示例

当您在设计器中时，切换到SQL视图。复制/粘贴我的查询并保存它。例如，将其命名为QRY_foo

现在它将充当一个表

然后在您的VBA中，只需这样做：

strSql2 = "SELECT * FROM QRY_Foo"
Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

然后，您将可以更容易地调整查询，只需从查询设计器进行调整即可。