不明确的列名错误

问题描述

在 Microsoft SQL Server 2000 上执行以下(完整)SQL 查询时:

When executing the following (complete) SQL query on Microsoft SQL Server 2000:

SELECT B.ARTIFACTTNS, B.ARTIFACTNAME, B.ARTIFACTTYPE, B.INITIALBYTES, B.TIMESTAMP1, B.FILENAME, B.BACKINGCLASS,
       B.CHARENCODING, B.APPNAME, B.COMPONENTTNS, B.COMPONENTNAME, B.SCAMODULENAME, B.SCACOMPONENTNAME
FROM (SELECT DISTINCT A.ARTIFACTTYPE, A.ARTIFACTTNS, A.ARTIFACTNAME
      FROM (SELECT DISTINCT ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME
            FROM CUSTPROPERTIES WHERE PNAME = 'AcmeSystemName' AND PVALUE = 'MyRuleGroup'
                  UNION SELECT DISTINCT ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME
                          FROM CUSTPROPERTIES WHERE PNAME = 'AcmeSystemDisplayName' AND PVALUE = 'MyRuleGroup') A,
           (SELECT DISTINCT ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME
            FROM CUSTPROPERTIES WHERE PNAME = 'AcmeSystemTargetNameSpace' AND PVALUE = 'http://MyModule') B
WHERE A.ARTIFACTTYPE = B.ARTIFACTTYPE AND A.ARTIFACTTNS = B.ARTIFACTTNS AND A.ARTIFACTNAME = B.ARTIFACTNAME) A, BYTESTORE B
    WHERE (A.ARTIFACTTYPE = 'BRG') AND A.ARTIFACTTYPE = B.ARTIFACTTYPE AND A.ARTIFACTTNS = B.ARTIFACTTNS AND A.ARTIFACTNAME = B.ARTIFACTNAME
    ORDER BY ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME

我收到以下异常:

java.sql.SQLException: [Acme][SQLServer JDBC Driver][SQLServer]
    Ambiguous column name 'ARTIFACTTYPE'.

我在这里做错了什么，我该如何纠正?

What am I doing wrong here and how can I correct it?

推荐答案

因为 ARTIFACTTYPE 可以引用 A.ARTIFACTTYPE 或 B.ARTIFACTTYPE> 并且服务器需要知道您想要哪个，只需将其更改为 A.ARTIFACTTYPE 就可以了.

Because ARTIFACTTYPE can refer to either A.ARTIFACTTYPE or B.ARTIFACTTYPE and the server needs to know which one you want, just change it to A.ARTIFACTTYPE and you should be okay in this case.

为了澄清，您需要在列名不明确时指定别名前缀.始终使用别名前缀是一种不错的做法，因为它可以在您阅读查询时明确哪些列来自哪些表，并消除了此类问题.

To clarify, you need to specify the alias prefix any time the column name is ambiguous. It isn't bad practice to always use alias prefixes as it makes it clear which columns are coming from which tables when you read the query, and eliminates issues like this one.

有人可能想知道，当它们都引用同一个表中的同一列时，为什么需要区分您想要的两列中的哪一列.答案是，当您将表与自身连接时，来自 A.column 和 B.column 的值可能会因连接条件不同而不同(例如可能是外部连接的情况，其中一列中的值可能是空).

One might wonder why you need to distinguish between which of two columns you want when they both refer to the same column in the same table. The answer is that when you join a table to itself, the values from A.column and B.column may be different depending on the join criteria (such as may be the case with an outer join where values in one of the columns may be null).

这篇关于不明确的列名错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！