sql - Excel VBA (ADODB) 中的嵌套连接结果为 "JOIN expression not supported"

Question

我有一系列三个表，我想在 Excel VBA 应用程序中使用 ADODB 将它们连接在一起。我正在使用以下查询，这会导致“不支持 JOIN 表达式”错误:

SELECT    tb1.date, 
          tb1.longID, 
          tb1.fld1,
          tb2.fld2,
          tb3.shortID,
          SUM(tb1.fld3) AS three, 
          SUM(tb1.fld4) AS four, 
          SUM(tb3.fld5) AS five
FROM      ([Table1$] AS tb1 LEFT JOIN [Table2$] AS tb2 ON tb1.longID = tb2.longID)
LEFT JOIN [Table3$]  AS tb3
ON        (tb3.shortID = tb2.shortID AND tb1.date = tb3.date)
GROUP BY  tb1.date, tb1.longID, tb3.shortID, tb2.fld3, tb1.fld2

如果我省略 shortID列对，查询工作正常。如果我省略 date列对，查询工作正常。但是一旦我将两者结合起来，那就是我遇到问题的时候。

任何帮助将不胜感激!

谢谢。

Answer 1

尝试让查询的 ON 部分内的所有内容都放在括号内。

JOIN 操作中的 ON 语句不完整或包含太多表。您可能希望将 ON 表达式放在 WHERE 子句中。

SELECT    tb1.date, 
          tb1.longID, 
          tb1.fld1,
          tb2.fld2,
          tb3.shortID,
          SUM(tb1.fld3) AS three, 
          SUM(tb1.fld4) AS four, 
          SUM(tb3.fld5) AS five
FROM      
[Table1$] AS tb1 
LEFT JOIN [Table2$] AS tb2 ON (tb1.longID = tb2.longID)
LEFT JOIN [Table3$]  AS tb3 ON (tb3.shortID = tb2.shortID)
WHERE tb1.date = tb3.date
GROUP BY  tb1.date, tb1.longID, tb3.shortID, tb2.fld3, tb1.fld2