通过 ODBC 使用 MS Access 2003 的嵌套内连接

发布于 2024-10-31 17:29:31 字数 683 浏览 2 评论 0原文

如果这有效:

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技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

逆流 2024-11-07 17:29:31
SELECT COUNT(t3.ID) AS count FROM Wall t3
INNER JOIN (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)
ON t3.Project_number=t1.Project_number

也许这只是一个语法错误? Office 帮助位于其底部提到嵌套。另一种可能性是别名以某种方式限定了范围,因此它们不可用于连接,但我不是 MS Access 方面的专家。也许您应该尝试完全删除别名。

SELECT COUNT(t3.ID) AS count FROM Wall t3
INNER JOIN (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)
ON t3.Project_number=t1.Project_number

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.

凉世弥音 2024-11-07 17:29:31

您的代码存在一些小问题:以下划线字符 (_Equipment_id) 开头的表名和作为 SQL 的 AS 子句(“别名”)关键字(AS 计数)。更正这些问题后,您的 SQL 就是有效的 SQL-92 语法。

遗憾的是,问题是 Access(ACE、Jet,等等)不支持 SQL-92 标准。 Access 坚持将每个嵌套的 JOIN 子句放在括号中。

[旁白:标准 SQL 中允许使用括号中的 JOIN,因为它可能会更改查询结果。然而,Access 不遵守编码器指定的顺序,并允许自己按照它看到的顺序来评估 JOIN。因此,不仅 Access 的语法不符合标准,而且还丧失了功能!但是,Access 的这一进一步问题不会对该特定查询产生不良影响。]

您在此处的同一范围内有两个 JOIN

...
INNER JOIN Project t1 ON t3.Project_number=t1.Project_number
INNER JOIN
...

您的代码需要通过包含 来解决 Access 的问题JOIN 放在括号中;因为所有 JOIN 都是 INNER 风格,所以它们去哪里可能并不重要。

另外,关于更正 AS 子句,Access 再次不支持标准 SQL 的带引号标识符 (...AS "count"...),并坚持要求您使用其专有方括号语法(...AS [count]...)——当然,您可以选择不同的名称,但可能存在依赖它的应用程序代码。

解决这两个访问问题的代码:

SELECT COUNT(t3.ID) AS [count] 
  FROM (Wall t3
       INNER JOIN Project AS t1 
          ON t3.Project_number = t1.Project_number)
       INNER JOIN (
                   SELECT DISTINCT t.Site,t.id 
                     FROM _Equipment_id AS t 
                    WHERE t.OEM LIKE '%ABC%'
                  ) AS t2 
          ON t1.Site = t2.Site 
             AND t1.id = t2.id;

You have a couple of minor issues with your code: a table name that starts with an underscore character (_Equipment_id) and an AS 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: JOINs 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 evaluate JOINs 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 JOINs in the same scope here:

...
INNER JOIN Project t1 ON t3.Project_number=t1.Project_number
INNER JOIN
...

Your code needs to work around Access's problem by enclosing the JOIN in parentheses; because all your JOINs are INNER 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:

SELECT COUNT(t3.ID) AS [count] 
  FROM (Wall t3
       INNER JOIN Project AS t1 
          ON t3.Project_number = t1.Project_number)
       INNER JOIN (
                   SELECT DISTINCT t.Site,t.id 
                     FROM _Equipment_id AS t 
                    WHERE t.OEM LIKE '%ABC%'
                  ) AS t2 
          ON t1.Site = t2.Site 
             AND t1.id = t2.id;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文