SQL 不明确的列名错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/318066/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Ambiguous column name error
提问by Paul Reiners
When executing the following (complete) SQL query on Microsoft SQL Server 2000:
在 Microsoft SQL Server 2000 上执行以下（完整）SQL 查询时：
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
I get the following exception:
我收到以下异常：
java.sql.SQLException: [Acme][SQLServer JDBC Driver][SQLServer]
Ambiguous column name 'ARTIFACTTYPE'.
What am I doing wrong here and how can I correct it?
我在这里做错了什么，我该如何纠正？
回答by Robert Gamble
Because ARTIFACTTYPEcan refer to either A.ARTIFACTTYPEor B.ARTIFACTTYPEand the server needs to know which one you want, just change it to A.ARTIFACTTYPEand you should be okay in this case.
因为ARTIFACTTYPE可以引用A.ARTIFACTTYPEorB.ARTIFACTTYPE并且服务器需要知道您想要哪个，只需将其更改为A.ARTIFACTTYPE，在这种情况下您应该没问题。
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.
为了澄清，您需要在列名不明确时指定别名前缀。始终使用别名前缀是一种不错的做法，因为它可以在您阅读查询时明确哪些列来自哪些表，并消除了此类问题。
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).
有人可能想知道，当它们都引用同一个表中的同一列时，为什么需要区分您想要的两列中的哪一列。答案是，当您将表与自身连接时，来自 A.column 和 B.column 的值可能会因连接条件不同而不同（例如可能是外部连接的情况，其中一列中的值可能是空值）。
回答by Dave Costa
If that's the exact query you're running, I have no idea why it would find anything ambiguous.
如果这是您正在运行的确切查询，我不知道为什么它会发现任何模棱两可的东西。
I wrote what I think is an equivalent query and ran it in my database (Oracle) with no problem.
我编写了我认为是等效的查询并在我的数据库 (Oracle) 中运行它，没有问题。
EDITAdding exact output of a new experiment in Oracle. The query executed in this experiment is the exact query given by the OP, with the table name filled in. NO OTHER CHANGES. There's nothing ambiguous in this query. So, either that is not the exact query that is being executed, or SQL Server has a parser bug.
编辑在 Oracle 中添加新实验的精确输出。本实验中执行的查询是 OP 给出的确切查询，填写了表名。 NO OTHER CHANGES。这个查询没有任何歧义。因此，要么这不是正在执行的确切查询，要么 SQL Server 存在解析器错误。
SQL> create table props (pname varchar2(100),
2 pvalue varchar2(100),
3 artifacttype number,
4 artifacttns number,
5 artifactname number);
Table created.
SQL> SELECT
2 DISTINCT A.ARTIFACTTYPE, A.ARTIFACTTNS, A.ARTIFACTNAME
3 FROM
4 (SELECT DISTINCT
5 ARTIFACTTYPE,
6 ARTIFACTTNS,
7 ARTIFACTNAME
8 FROM props
9 WHERE PNAME = 'AcmeSystemName'
10 AND PVALUE = 'MyRuleGroup'
11 UNION
12 SELECT DISTINCT
13 ARTIFACTTYPE,
14 ARTIFACTTNS,
15 ARTIFACTNAME
16 FROM props
17 WHERE PNAME = 'AcmeSystemDisplayName'
18 AND PVALUE = 'MyRuleGroup') A,
19 (SELECT DISTINCT
20 ARTIFACTTYPE,
21 ARTIFACTTNS,
22 ARTIFACTNAME
23 FROM props
24 WHERE PNAME = 'AcmeSystemTargetNameSpace'
25 AND PVALUE = 'http://mymodule') B
26 WHERE A.ARTIFACTTYPE = B.ARTIFACTTYPE
27 AND A.ARTIFACTTNS = B.ARTIFACTTNS
28 AND A.ARTIFACTNAME = B.ARTIFACTNAME
29 /
no rows selected
End Edit
结束编辑
My suggestion for getting around the error is to give the table in each select clause a unique alias and qualify all column references. Like this:
我解决该错误的建议是为每个 select 子句中的表指定一个唯一别名并限定所有列引用。像这样：
SELECT
DISTINCT A.ARTIFACTTYPE, A.ARTIFACTTNS, A.ARTIFACTNAME
FROM
(SELECT DISTINCT
P1.ARTIFACTTYPE,
P1.ARTIFACTTNS,
P1.ARTIFACTNAME
FROM {PROPERTIES_TABLE_NAME} P1
WHERE PNAME = 'AcmeSystemName'
AND PVALUE = 'MyRuleGroup'
UNION
SELECT DISTINCT
P2.ARTIFACTTYPE,
P2.ARTIFACTTNS,
P2.ARTIFACTNAME
FROM {PROPERTIES_TABLE_NAME} P2
WHERE PNAME = 'AcmeSystemDisplayName'
AND PVALUE = 'MyRuleGroup') A,
(SELECT DISTINCT
P3.ARTIFACTTYPE,
P3.ARTIFACTTNS,
P3.ARTIFACTNAME
FROM {PROPERTIES_TABLE_NAME} P3
WHERE PNAME = 'AcmeSystemTargetNameSpace'
AND PVALUE = 'http://mymodule') B
WHERE A.ARTIFACTTYPE = B.ARTIFACTTYPE
AND A.ARTIFACTTNS = B.ARTIFACTTNS
AND A.ARTIFACTNAME = B.ARTIFACTNAME
回答by kristof
Are you listing the complete query? Perhaps you have also ORDER BY clause - that could cause that problem
您是否列出了完整的查询？也许您还有 ORDER BY 子句 - 这可能会导致该问题
I would support Dave on that thatthere should be no problem with the posted query
我会支持戴夫，因为发布的查询应该没有问题
回答by Paul Reiners
You need to specify the tables in the ORDER BYclause, like this:
您需要在ORDER BY子句中指定表，如下所示：
ORDER BY A.ARTIFACTTYPE, A.ARTIFACTTNS, A.ARTIFACTNAME
回答by Logicalmind
To be clear, it is lines 13, 14 and 15 that have the ambiguous columns.
需要明确的是，第 13、14 和 15 行具有不明确的列。
