sql - VBA SQL 右连接 - 连接操作中的语法错误

Question

我正在尝试在 Excel VBA 中正确加入子查询，但我不断收到语法错误。谁能发现它？

SELECT c.Category
FROM [Catalogue Info] as c

RIGHT JOIN (
SELECT [Product Code]
FROM Orders
WHERE JSID = 10503001
AND [Delivery Date] >= 04/12/2017
GROUP BY [Product Code]
) AS o ON c.code = o.[Product Code]

WHERE c.Brand='Brand'
AND c.Category<>''
GROUP BY c.Category
ORDER BY Avg(c.Page) Asc;

Excel 2016 视窗 10

- -编辑 - -

这是产生上述查询的 VBA 中的实际代码

sqlProdList = " RIGHT JOIN" _
    & " (SELECT [Product Code]" _
    & " FROM Orders" _
    & " WHERE JSID = " & customerRef _
    & " AND [Delivery Date] >= " & Date - 365 _
    & " GROUP BY [Product Code]) as o on c.code = o.[Product Code]"

sqlCategory = "SELECT c.Category" _
    & " FROM [Catalogue Info] as c" _
    & sqlProdList _
    & " WHERE c.Brand='Brand'" _
    & " AND c.Category<>''" _
    & " GROUP BY c.Category" _
    & " ORDER BY Avg(c.Page) Asc;"

这是表格的样子：

标签： sqlexcelvba

Answer 1

我怀疑你想要的查询更像这样：

SELECT c.Category
FROM [Catalogue Info] as c LEFT JOIN 
     [Product Code]
     o.Orders o
     ON c.code = o.[Product Code] AND
        o.JSID = 10503001 AND
        [Delivery Date] >= '2017-04-12'
WHERE c.Brand = 'Brand' AND c.Category <> ''
GROUP BY c.Category
ORDER BY Avg(c.Page) Asc;

笔记：

• 这使用LEFT JOIN.
• 它修复了日期格式。
• 它删除子查询。