sqlite 在Microsoft Access中创建包含CROSS JOIN后跟LEFT JOIN的查询时出现问题

igsr9ssn 于 2023-05-18 发布在 SQLite

关注(0)|答案(1)|浏览(194)

我正在尝试将以下SQLite查询转换为MS-ACCESS（Microsoft Access 2016）查询，但没有成功：
SQLite中的查询：

SELECT c.CategoryName, s.SubCategoryName, COUNT(l.LoanDate) AS Count
FROM LoanCategories c CROSS JOIN LoanSubCategories s
LEFT JOIN Loans l
ON l.LoanCatId = c.CategoryId AND l.LoanSubCatId = s.CategoryId AND l.LoanDate = '19990125'
GROUP BY c.CategoryName, s.CategoryName;

我在MS-ACCESS中尝试了以下查询，导致了'Syntax error in JOIN operation'错误：
我在MS-ACCESS中的查询：

SELECT c.CategoryName, s.SubCategoryName, COUNT(l.LoanDate) AS Count
FROM LoanCategories c, LoanSubCategories s
LEFT JOIN Loans l
ON l.LoanCatId = c.CategoryId AND l.LoanSubCatId = s.CategoryId AND l.LoanDate = '19990125'
GROUP BY c.CategoryName, s.CategoryName;

如何在MS-ACCESS中重写查询？

sqlite

来源：https://stackoverflow.com/questions/76194548/problem-in-creating-the-query-containing-a-cross-join-followed-by-a-left-join-in

1条答案

按热度按时间

k97glaaz1#

MS-Access中的语法完全不同。
不支持显式的CROSS连接，您必须使用旧的逗号语法。
此外，联接的ON子句不能包含像LoanDate = '19990125'这样的表达式。
查询可以这样写：

SELECT t.CategoryName, t.SubCategoryName, Nz(l.Count, 0) AS Count
FROM (
  SELECT c.CategoryId, c.CategoryName, 
         s.CategoryId AS SubCategoryId, s.CategoryName AS SubCategoryName
  FROM LoanCategories AS c, LoanSubCategories AS s
) AS t
LEFT JOIN (
  SELECT LoanCatId, LoanSubCatId, COUNT(*) AS Count
  FROM Loans
  WHERE LoanDate = '19990125'
  GROUP BY LoanCatId, LoanSubCatId
) AS l ON l.LoanCatId = t.CategoryId AND l.LoanSubCatId = t.SubCategoryId;

赞(0）回复(0）举报 2023-05-18

我来回答

sqlite 在Microsoft Access中创建包含CROSS JOIN后跟LEFT JOIN的查询时出现问题

1条答案

相关问题

热门标签

最新问答