将 TSQL 转换为 MS-Access SQL

发布于 2024-07-18 22:13:12 字数 1259 浏览 13 评论 0原文

TSQL(在 MS SQL Server 2000 和 2005 中使用)允许多个 JOIN 子句,一个接一个,不需要逗号或括号。 在 Access 中尝试一下,结果很奇怪: “查询表达式中存在语法错误(缺少运算符)...”

根据我在 Google 中收集到的信息,Access SQL 需要括号对 JOIN 子句进行分组。 关于如何完成此任务的大多数建议是使用设计视图或查询向导,并让 Access 确定在哪里放置括号(标准 SQL 中不需要)。 问题是,我太习惯在文本编辑器(记事本、SSMS、VS2005,等等)中执行 SQL,设计视图和向导会妨碍我,让我感到毛骨悚然。 有时,如果有多种可能性,向导会对加入什么做出错误的假设,而我已经习惯了自己在 TSQL 中这样做,因此我宁愿将向导排除在外。

是否没有一个工具可以将 TSQL 转换为 Access SQL,或者至少有一组关于括号放置位置的规则?

例子:

SELECT ...
FROM Participant PAR
    INNER JOIN Individual IND 
        ON PAR.APETSID = IND.APETSID
    INNER JOIN Ethnicity ETH 
        ON IND.EthnicityID = ETH.ID
    INNER JOIN Education EDU 
        ON IND.EducationID = EDU.ID
    INNER JOIN Marital MAR 
        ON IND.Marital = MAR.ID
    INNER JOIN Participant-Probation PXP 
        ON PAR.ID = PXP.ParticipantID
    INNER JOIN Probation PBN 
        ON PXP.ProbationID = PBN.ID
    INNER JOIN Class-Participant CXP 
        ON PAR.ID = CXP.ParticipantID
    INNER JOIN Class CLS 
        ON CXP.ClassID = CLS.ID
    INNER JOIN Official OFR 
        ON PAR.ReferringPO = OFR.ID
    INNER JOIN Participant-Official PXO 
        ON PAR.ID = PXO.ParticipantID
    INNER JOIN Official OFA 
        ON PXO.OfficialID = OFA.ID

TSQL (as used in MS SQL Server 2000 and 2005) allows multiple JOIN clauses, one right after the other, no commas or parentheses needed. Try this in Access and it throws a fit:
"Syntax error (missing operator) in query expression ... "

From what I have been able to gather out in Google-land, Access SQL wants parentheses to group the JOIN clauses. Most advice on how to accomplish this is to use the design view or the query wizard, and let Access figure out where to put the parentheses (that are NOT required in standard SQL). Problem is, I am so used to doing my SQL in a text editor (Notepad, SSMS, VS2005, whatever) that the design view and the wizard get in the way and make my skin crawl. Sometimes the wizards make bad assumptions about what to join if there are multiple possibilities, and I'm so used to doing it myself in TSQL that I'd rather leave the wizards out of it.

Isn't there a tool that will convert TSQL into Access SQL, or at least a set of rules on where to put the parentheses?

Example:

SELECT ...
FROM Participant PAR
    INNER JOIN Individual IND 
        ON PAR.APETSID = IND.APETSID
    INNER JOIN Ethnicity ETH 
        ON IND.EthnicityID = ETH.ID
    INNER JOIN Education EDU 
        ON IND.EducationID = EDU.ID
    INNER JOIN Marital MAR 
        ON IND.Marital = MAR.ID
    INNER JOIN Participant-Probation PXP 
        ON PAR.ID = PXP.ParticipantID
    INNER JOIN Probation PBN 
        ON PXP.ProbationID = PBN.ID
    INNER JOIN Class-Participant CXP 
        ON PAR.ID = CXP.ParticipantID
    INNER JOIN Class CLS 
        ON CXP.ClassID = CLS.ID
    INNER JOIN Official OFR 
        ON PAR.ReferringPO = OFR.ID
    INNER JOIN Participant-Official PXO 
        ON PAR.ID = PXO.ParticipantID
    INNER JOIN Official OFA 
        ON PXO.OfficialID = OFA.ID

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

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

评论(2

沦落红尘 2024-07-25 22:13:12

是的,MS-Access 很愚蠢。

我认为不存在(从 MS-SQL/TSQL 到 MS-Access 可能也不是一个巨大的市场)。 通常,我使用设计视图,就我而言,它并不是真正的向导。 然后,我手动添加表,然后(如果我还没有创建正确的关系图,或者有些东西有点奇怪)在设计器中手动创建关系。 之后,我在 SQL 视图中检查查询并根据需要进行更正。

在您的示例中(正如您所指出的),您可能需要括号,并且必须手动添加它们。 您可能想要这样的东西:(

SELECT ...
FROM (((Participant PAR
    INNER JOIN Individual IND 
        ON PAR.APETSID = IND.APETSID)
    INNER JOIN Ethnicity ETH 
        ON IND.EthnicityID = ETH.ID)
    INNER JOIN Education EDU 
        ON IND.EducationID = EDU.ID)
    INNER JOIN Marital MAR 
        ON IND.Marital = MAR.ID

如果您有 N 个内部联接,则在开头需要 N-1 个左括号,并且在联接的任意末尾需要一个;不包括最后一个)

Yah, MS-Access is dumb.

I don't think one exists (probably not a huge market either to go from MS-SQL/TSQL to MS-Access). Typically, I use the Design View which is not really a wizard as far as I'm concerned. I then manually add the tables, and then (if I haven't created a proper Relations ship diagram, or something is a little funky) manually create the relationships in the Designer. After that, I check the query in the SQL view and correct as need be.

In the case of your example (as you indicated) you probably need the parenthesis, and will have to manually add them. You probably want something like this:

SELECT ...
FROM (((Participant PAR
    INNER JOIN Individual IND 
        ON PAR.APETSID = IND.APETSID)
    INNER JOIN Ethnicity ETH 
        ON IND.EthnicityID = ETH.ID)
    INNER JOIN Education EDU 
        ON IND.EducationID = EDU.ID)
    INNER JOIN Marital MAR 
        ON IND.Marital = MAR.ID

(if you have N inner joins, you will need N-1 open-parenthesis at the beginning, and one on ever end of the join; excluding the last one)

逐鹿 2024-07-25 22:13:12

这在 Access 中有效。

SELECT *
FROM (((Individual AS IND 

INNER JOIN Ethnicity AS ETH 
    ON IND.EthnicityID = ETH.ID) 

INNER JOIN Education AS EDU 
    ON IND.EducationID = EDU.ID) 

INNER JOIN Marital AS MAR 
    ON IND.Marital = MAR.ID) 

INNER JOIN (((((((Participant AS PAR 

    INNER JOIN Official AS OFR 
        ON PAR.ReferringPO = OFR.ID) 

    INNER JOIN [Class-Participant] AS CXP 
        ON PAR.ID = CXP.ParticipantID) 

    INNER JOIN Class AS CLS 
        ON CXP.ClassID = CLS.ID) 

    INNER JOIN [Participant-Official] AS PXO 
        ON PAR.ID = PXO.ParticipantID) 

    INNER JOIN Official AS OFA 
        ON PXO.OfficialID = OFA.ID) 

    INNER JOIN [Participant-Probation] AS PXP 
        ON PAR.ID = PXP.ParticipantID) 

    INNER JOIN Probation AS PBN 
        ON PXP.ProbationID = PBN.ID) 

 ON IND.APETSID = PAR.APETSID

正如您所看到的,要连接的表被分组在一起。

This works in Access.

SELECT *
FROM (((Individual AS IND 

INNER JOIN Ethnicity AS ETH 
    ON IND.EthnicityID = ETH.ID) 

INNER JOIN Education AS EDU 
    ON IND.EducationID = EDU.ID) 

INNER JOIN Marital AS MAR 
    ON IND.Marital = MAR.ID) 

INNER JOIN (((((((Participant AS PAR 

    INNER JOIN Official AS OFR 
        ON PAR.ReferringPO = OFR.ID) 

    INNER JOIN [Class-Participant] AS CXP 
        ON PAR.ID = CXP.ParticipantID) 

    INNER JOIN Class AS CLS 
        ON CXP.ClassID = CLS.ID) 

    INNER JOIN [Participant-Official] AS PXO 
        ON PAR.ID = PXO.ParticipantID) 

    INNER JOIN Official AS OFA 
        ON PXO.OfficialID = OFA.ID) 

    INNER JOIN [Participant-Probation] AS PXP 
        ON PAR.ID = PXP.ParticipantID) 

    INNER JOIN Probation AS PBN 
        ON PXP.ProbationID = PBN.ID) 

 ON IND.APETSID = PAR.APETSID

As you can see, the tables to be joined are grouped together.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文