将TSQL转换为MS-Access SQL [英] Convert TSQL to MS-Access SQL

问题描述

TSQL(在MS SQL Server 2000和2005中使用)允许多个JOIN子句，一个紧接另一个，不需要逗号或括号.在Access中尝试使用此方法，它很合适: 查询表达式中的语法错误(缺少运算符)..."

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 ... "

根据我在Google领域所能收集到的信息，Access SQL希望用括号将JOIN子句分组.关于如何完成此操作的大多数建议是使用设计视图或查询向导，然后让Access找出放置括号的位置(标准SQL不需要).问题是，我习惯于在文本编辑器(记事本，SSMS，VS2005等)中执行SQL，以至于设计视图和向导妨碍了我的皮肤的爬行.有时，如果存在多种可能性，向导会对要加入的内容做出错误的假设，而我习惯于在TSQL中自己做，所以我宁愿将向导排除在外.

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.

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

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?

示例:

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

推荐答案

是的，MS-Access是愚蠢的.

Yah, MS-Access is dumb.

我认为不存在(从MS-SQL/TSQL到MS-Access可能都不是一个巨大的市场).通常，我使用的Design View实际上并不是一个向导.然后，我手动添加表，然后(如果我尚未创建正确的关系"运送图，或者有些时髦)，则在Designer中手动创建关系.之后，我在SQL视图中检查查询并根据需要进行更正.

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

(如果您有N个内部联接，则在开始时将需要N-1个开放括号，并且在联接的末尾将需要一个；并且不包括最后一个)

(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)

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