VBA调用ADO执行含LEFT JOIN的TRANSFORM SQL语句报语法错误

Excel 调用的 ACE OLEDB 12.0 驱动对 TRANSFORM 语句的语法校验比 Access 更严格：所有出现在 PIVOT、ORDER BY 子句的非聚合字段，必须全部在 GROUP BY 子句中显式声明。你当前的 SQL 里 PIVOT 和 ORDER BY 用到的 MESI$.MESE 字段未加入 GROUP BY 列表，因此触发语法报错。

只需在 GROUP BY 子句中补充 [MESI$].MESE 字段即可，也可以给工作表名加上方括号提升兼容性，修改后的完整代码如下：

Sub generaRicavi()
    Dim RS As New ADODB.Recordset
    Dim conn As String, SQL As String
    
    Application.ScreenUpdating = False
    conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & "; Jet OLEDB:Bypass ChoiceField Validation =True;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"
            
    SQL = "TRANSFORM Sum([Foglio1$].RICAVI)" _
            & " SELECT Year([Foglio1$].[DATA])" _
            & " FROM [MESI$] LEFT JOIN [Foglio1$] ON [MESI$].MESE =  Month([Foglio1$].[DATA])" _
            & " GROUP BY Year([Foglio1$].[DATA]), [MESI$].MESE" _
            & " ORDER BY Year([Foglio1$].[DATA]), [MESI$].MESE" _
            & " PIVOT [MESI$].MESE;"
            
    RS.Open SQL, conn, adOpenStatic, adLockReadOnly
    
    With Foglio2
        .Range("A3").CurrentRegion.ClearContents
        .Range("A3:M3") = Array("ANNO","GENNAIO", "FEBBRAIO", "MARZO", "APRILE", "MAGGIO", "GIUGNO", "LUGLIO", "AGOSTO", "SETTEMBRE", "OTTOBRE", "NOVEMBRE", "DICEMBRE")
        .Range("A4").CopyFromRecordset RS
    End With
    
    RS.Close
    Application.ScreenUpdating = True
    Set RS = Nothing
End Sub

如果仍有兼容问题，可以改用子查询写法，语法适配性更强：

SQL = "TRANSFORM Sum(RICAVI) " _
        & " SELECT ANNO " _
        & " FROM (SELECT Year([Foglio1$].[DATA]) AS ANNO, [MESI$].MESE, [Foglio1$].RICAVI " _
        & " FROM [MESI$] LEFT JOIN [Foglio1$] ON [MESI$].MESE = Month([Foglio1$].[DATA])) " _
        & " GROUP BY ANNO " _
        & " ORDER BY ANNO, MESE " _
        & " PIVOT MESE;"

内容的提问来源于stack exchange，提问作者kris9951