SQL JOIN查询触发运行时错误'-2147217900(80040e14)'求助

在Excel VBA的Step2_CreateReportSheetWithConn过程中，使用ADODB连接执行testQuery函数生成的SQL查询时，触发运行时错误'-2147217900(80040e14)'，无额外提示。问题出在以下双LEFT JOIN语句部分：

"LEFT JOIN (" & q_1 & ") AS a ON c.[inn] = a.inn AND c.[seg] = a.seg " & _
"LEFT JOIN (" & q_2 & ") AS b ON c.[seg] = b.seg " & _

现象 #

• 移除任意一条JOIN语句，查询可正常执行；
• 调换JOIN顺序、修改关联键均无效；
• 单独执行其他查询部分无异常。

相关代码 #

Step2_CreateReportSheetWithConn过程

Sub Step2_CreateReportSheetWithConn()
    Dim ws_from$, ws_to$, group_name$
    ws_from = "Data"
    ws_to = "Result"

    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
    conn.Open

    Dim aggQuery$
    aggQuery = testQuery() ' 原代码中testQ()为笔误，应为testQuery()
    Set rs = CreateObject("ADODB.Recordset")
    ThisWorkbook.Sheets(ws_from).Range("A:B,J:L").NumberFormat = "0" 
    rs.Open aggQuery, conn, 1, 3 ' 触发运行时错误'-2147217900(80040e14)'
    If Not rs.EOF Then
        ThisWorkbook.Sheets(ws_to).Cells(6, 1).CopyFromRecordset rs
        rs.Close
    End If
        
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

testQuery函数

Function testQuery()
''' Returns result query from parts combine '''
    
    q_1 = _
        "SELECT [seg], [inn], SUM([kg]) " & _
        "FROM [Данные$] " & _
        "GROUP BY [seg], [inn] "
            
    q_2 = _
        "SELECT [seg], COUNT([inn]), SUM([kg]) " & _
        "FROM [Данные$] " & _
        "GROUP BY [seg] "

    testQuery = _
        "SELECT " & _
            "c.[seg], c.[sku]," & _
       "COUNT(c.[inn]) / COUNT(b.[inn]) as DistrNV, " & _
            "SUM(a.[kg]) / SUM(b.[kg]) as DistrV, " & _
        "FROM [Данные$] AS c " & _
        "LEFT JOIN (" & q_1 & ") AS a ON c.[inn] = a.inn AND c.[seg] = a.seg " & _
        "LEFT JOIN (" & q_2 & ") AS b ON c.[seg] = b.seg " & _
        "GROUP BY c.[seg], c.[sku]"
End Function

问题根源及修复方案 #

1. 子查询聚合字段未命名：ACE OLEDB引擎处理多表JOIN时，无法识别未指定别名的聚合字段，需给q_1、q_2中的SUM、COUNT字段添加别名。
2. 主查询语法错误：主SELECT语句最后一个字段SUM(a.[kg]) / SUM(b.[kg]) as DistrV后多了一个逗号，违反SQL语法规范。
3. 函数名笔误：原代码中aggQuery = testQ()应为aggQuery = testQuery()，否则会因函数未定义报错。

修复后的testQuery函数代码：

Function testQuery()
''' Returns result query from parts combine '''
    
    q_1 = _
        "SELECT [seg], [inn], SUM([kg]) AS sum_kg " & _ ' 给聚合字段添加别名
        "FROM [Данные$] " & _
        "GROUP BY [seg], [inn] "
            
    q_2 = _
        "SELECT [seg], COUNT([inn]) AS count_inn, SUM([kg]) AS sum_kg " & _ ' 给聚合字段添加别名
        "FROM [Данные$] " & _
        "GROUP BY [seg] "

    testQuery = _
        "SELECT " & _
            "c.[seg], c.[sku]," & _
            "COUNT(c.[inn]) / COUNT(b.count_inn) as DistrNV, " & _ ' 使用子查询的别名
            "SUM(a.sum_kg) / SUM(b.sum_kg) as DistrV " & _ ' 移除末尾逗号，使用子查询的别名
        "FROM [Данные$] AS c " & _
        "LEFT JOIN (" & q_1 & ") AS a ON c.[inn] = a.inn AND c.[seg] = a.seg " & _
        "LEFT JOIN (" & q_2 & ") AS b ON c.[seg] = b.seg " & _
        "GROUP BY c.[seg], c.[sku]"
End Function

修复后，ADODB连接可正常执行SQL查询，不会触发运行时错误。

内容的提问来源于stack exchange，提问作者Александр Зикеев