将 MySQL 查询转换为 MS Access [英] Translating MySQL Queries to MS Access

问题描述

我有一个使用 MySQL 的非常古老的项目，我正在考虑将其部分转换为 MS Access.我遇到了一些更复杂的查询问题，想知道是否有参考资料详细说明了 Access 的 SQL 和 MySQL 之间的差异.例如，我有以下查询:

I have a very old project which uses MySQL, which I am considering converting part of to MS Access. I'm running into problems with some of the more complex queries, and wondered if there is a reference which details the differences between Access's SQL and MySQL. For example, I have the following query:

select P.PersonID, P.FirstName, P.MiddleName, P.LastName,
       PR.LastName as MarriedName, P.Born, LocID, PlaceName,
       City, County, State, Country
from   persons P
         left join relatives R on (R.Person = P.PersonID and TookName)
         left join persons PR on (PR.PersonID = R.Relative)
         left join locations L on (L.Person = P.PersonID and L.FromDate = P.Born)
where  not P.Deleted
  and  (P.FirstName in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al')
   or   P.MiddleName in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al')
   or   P.Nickname in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al'))
  and  (P.LastName = 'Little' or PR.LastName = 'Little')
group  by P.PersonID
order  by P.Born desc

在 Access 中，我可以达到第一个 join:

In Access, I can get as far as the first join:

select P.PersonID, P.FirstName, P.MiddleName, P.LastName,
       PR.LastName as MarriedName, P.Born
from   persons P
         left join relatives R on (R.Person = P.PersonID and TookName)
where  not P.Deleted
  and  P.FirstName in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al')

如果我添加第二个连接，它会说，查询表达式中的语法错误(缺少运算符)'(R.Person = P.PersonID 和 TookName)将人 PR 留在 (PR.PersonID = R.Relative.'

if I add the second join it says, Syntax error (missing operator) in query expression '(R.Person = P.PersonID and TookName) left join persons PR on (PR.PersonID = R.Relative.'

单击帮助"按钮非常有用地通知我，由于消息中指出的原因，您输入的表达式无效.谢谢！

Clicking the Help button very helpfully informs me, The expression you typed is not valid for the reason indicated in the message. Gee thanks!

但是我还有其他一些相当复杂的查询，所以除了解决这个问题之外，我还在寻找可以解释一般差异的东西.

But I have some other rather complex queries, so beyond solving the problem with this one, I'm looking for something that will explain the differences in general.

所以，我根据链接到的答案更改了查询:

So, I changed the query according to the answer linked to:

select P.PersonID, P.FirstName, P.MiddleName, P.LastName,
       PR.LastName as MarriedName, P.Born
from   (persons P
         left join relatives R on R.Person = P.PersonID and TookName=true)
         left join persons PR on PR.PersonID = R.Relative
where  not P.Deleted
  and  P.FirstName in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al')

它告诉我JOIN 表达式不受支持，并突出显示TookName=true.我也尝试将其作为 TookName=1 和 TookName.我尝试删除第二个 JOIN，第一个在括号中，但它仍然只是告诉我 JOIN 表达式不受支持.

It tells me JOIN expression not supported, and highlights TookName=true. I also tried it as TookName=1 and just TookName. I tried removing the second JOIN, with the first in parentheses, and it still just tells me JOIN expression not supported.

推荐答案

Access SQL 解析器非常喜欢括号.

The Access SQL parser is a huge fan of parentheses.

所有超过 2 个表的 JOIN 都需要它们

They are needed in all JOINs with more than 2 tables

FROM (a JOIN b ON a.id = b.id) JOIN c on b.id = c.id

这样只有两个表/子查询连接在一组括号中.

so that only two tables / subqueries are joined in one set of parentheses.

并且(正如我今天了解到的)如果您想在其中使用文字值，则在 ON 子句周围需要它们.

And (as I learned today) they are needed around the ON clause if you want to use literal values in it.

FROM (a JOIN b ON (a.id = b.id AND b.foo = True)) JOIN c on b.id = c.id

扩展说明是 此处.在此处找到链接:https://stackoverflow.com/a/23632282/3820271

An extended description is here. Link was found here: https://stackoverflow.com/a/23632282/3820271

这篇关于将 MySQL 查询转换为 MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！