通过 ODBC 使用 MS Access 2003 的嵌套内连接
如果这有效:
SELECT COUNT(t1.ID) AS count FROM Project t1
INNER JOIN (SELECT DISTINCT t.Site,t.id FROM _Equipment_id t WHERE t.OEM LIKE '%ABC%') t2 ON t1.Site=t2.Site AND t1.id=t2.id
并且这有效:
SELECT COUNT(t3.ID) AS count FROM Wall t3
INNER JOIN Project t1 ON t3.Project_number=t1.Project_number
为什么这不起作用:
SELECT COUNT(t3.ID) AS count FROM Wall t3
INNER JOIN Project t1 ON t3.Project_number=t1.Project_number
INNER JOIN (SELECT DISTINCT t.Site,t.id FROM _Equipment_id t WHERE t.OEM LIKE '%ABC%') t2 ON t1.Site=t2.Site AND t1.id=t2.id
最终,我有 10 个表,例如 Wall 表,我试图从第一个 SELECT 中获取总计数......
If this works:
SELECT COUNT(t1.ID) AS count FROM Project t1
INNER JOIN (SELECT DISTINCT t.Site,t.id FROM _Equipment_id t WHERE t.OEM LIKE '%ABC%') t2 ON t1.Site=t2.Site AND t1.id=t2.id
and this works:
SELECT COUNT(t3.ID) AS count FROM Wall t3
INNER JOIN Project t1 ON t3.Project_number=t1.Project_number
Why doesn't this work:
SELECT COUNT(t3.ID) AS count FROM Wall t3
INNER JOIN Project t1 ON t3.Project_number=t1.Project_number
INNER JOIN (SELECT DISTINCT t.Site,t.id FROM _Equipment_id t WHERE t.OEM LIKE '%ABC%') t2 ON t1.Site=t2.Site AND t1.id=t2.id
Ultimately, I have 10 tables like the Wall table that I am trying to get a total count from the first SELECT....
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
也许这只是一个语法错误? Office 帮助位于其底部提到嵌套。另一种可能性是别名以某种方式限定了范围,因此它们不可用于连接,但我不是 MS Access 方面的专家。也许您应该尝试完全删除别名。
Maybe it's just a syntax error? Office Help at the bottom where they mention nesting. The other possibility is that the aliases are somehow scoped so that they are not available to the join, but I'm no expert on MS Access. Maybe you should just try dropping the aliases altogether.
您的代码存在一些小问题:以下划线字符 (
_Equipment_id
) 开头的表名和作为 SQL 的AS
子句(“别名”)关键字(AS 计数
)。更正这些问题后,您的 SQL 就是有效的 SQL-92 语法。遗憾的是,问题是 Access(ACE、Jet,等等)不支持 SQL-92 标准。 Access 坚持将每个嵌套的
JOIN
子句放在括号中。[旁白:标准 SQL 中允许使用括号中的
JOIN
,因为它可能会更改查询结果。然而,Access 不遵守编码器指定的顺序,并允许自己按照它看到的顺序来评估 JOIN。因此,不仅 Access 的语法不符合标准,而且还丧失了功能!但是,Access 的这一进一步问题不会对该特定查询产生不良影响。]您在此处的同一范围内有两个
JOIN
:您的代码需要通过包含
来解决 Access 的问题JOIN
放在括号中;因为所有JOIN
都是INNER
风格,所以它们去哪里可能并不重要。另外,关于更正
AS
子句,Access 再次不支持标准 SQL 的带引号标识符 (...AS "count"...
),并坚持要求您使用其专有方括号语法(...AS [count]...
)——当然,您可以选择不同的名称,但可能存在依赖它的应用程序代码。解决这两个访问问题的代码:
You have a couple of minor issues with your code: a table name that starts with an underscore character (
_Equipment_id
) and anAS
clause ("alias") that is a SQL keyword (AS count
). When these are corrected, your SQL is valid SQL-92 syntax.Sadly, the problem is that Access (ACE, Jet, whatever) does not support the SQL-92 Standard. Access insists that each nested
JOIN
clause is put in parentheses.[Aside:
JOIN
s in parentheses are allowed in Standard SQL because it can potentially change the query results. However Access, does not respect the order specified by the coder and allows itself to evaluateJOIN
s in order it sees fir. So not only Access's syntax non-compliant with the Standard, there is also a loss of functionality! However, this further problem with Access will have no ill effect for this particular query.]You have two
JOIN
s in the same scope here:Your code needs to work around Access's problem by enclosing the
JOIN
in parentheses; because all yourJOIN
s areINNER
flavour, it probably doesn't matter where they go.Also, as regards correcting your
AS
clause, Access again doesn't support Standard SQL's quoted identifiers (...AS "count"...
) and insists you use its proprietary square brackets syntax (...AS [count]...
) -- of course, you could choose a different name but there may exist application code that relies on it.Code to workaround both Access problems: