SQL ORA-00918: 列在 SELECT * 中定义不明确
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6233086/
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
ORA-00918: column ambiguously defined in SELECT *
提问by user43685
Getting ORA-00918: column ambiguously defined: running this SQL:
获取 ORA-00918：列定义不明确：运行此 SQL：
SELECT *
FROM
(SELECT DISTINCT(coaches.id),
people.*,
users.*,
coaches.*
FROM "COACHES"
INNER JOIN people ON people.id = coaches.person_id
INNER JOIN users ON coaches.person_id = users.person_id
LEFT OUTER JOIN organizations_users ON organizations_users.user_id = users.id
) WHERE rownum <= 25
Any suggestions please?
请问有什么建议吗？
回答by APC
A query's projection can only have one instance of a given name. As your WHERE clause shows, you have several tables with a column called ID. Because you are selecting *your projection will have several columns called ID. Or it would have were it not for the compiler hurling ORA-00918.
一个查询的投影只能有一个给定名称的实例。正如您的 WHERE 子句所示，您有多个表，其中有一列名为 ID。因为您正在选择*您的投影将有几列称为 ID。或者，如果不是编译器抛出 ORA-00918，它就会出现。
The solution is quite simple: you will have to expand the projection to explicitly select named columns. Then you can either leave out the duplicate columns, retaining just (say) COACHES.ID or use column aliases: coaches.id as COACHES_ID.
解决方案非常简单：您必须扩展投影以显式选择命名列。然后你可以省略重复的列，只保留（比如）COACHES.ID 或使用列别名：coaches.id as COACHES_ID。
Perhaps that strikes you as a lot of typing, but it is the only way. If it is any comfort, SELECT *is regarded as bad practice in production code: explicitly named columns are much safer.
也许这会让您觉得打字很多，但这是唯一的方法。如果它有任何安慰，SELECT *在生产代码中被认为是不好的做法：显式命名的列更安全。
回答by Craig
You have multiple columns named the same thing in your inner query, so the error is raised in the outer query. If you get rid of the outer query, it should run, although still be confusing:
您的内部查询中有多个列名称相同，因此在外部查询中会引发错误。如果您摆脱了外部查询，它应该会运行，但仍然令人困惑：
SELECT DISTINCT
coaches.id,
people.*,
users.*,
coaches.*
FROM "COACHES"
INNER JOIN people ON people.id = coaches.person_id
INNER JOIN users ON coaches.person_id = users.person_id
LEFT OUTER JOIN organizations_users ON organizations_users.user_id = users.id
WHERE
rownum <= 25
It would be muchbetter (for readability and performance both) to specify exactly what fields you need from each of the tables instead of selecting them all anyways. Then if you really need two fields called the same thing from different tables, use column aliases to differentiate between them.
这将是很多更好的（用于可读性和性能两者）能明确指定字段，你从每个表，而不是选择他们所有的反正需要。然后，如果您确实需要从不同的表中调用相同内容的两个字段，请使用列别名来区分它们。
回答by C Payne
You can also see this error when selecting for a union where corresponding columns can be null.
在选择相应列可以为空的联合时，您也可以看到此错误。
select * from (select D.dept_no, D.nullable_comment
from dept D
union
select R.dept_no, NULL
from redundant_dept R
)
This apparently confuses the parser, a solution is to assign a column alias to the always null column.
这显然混淆了解析器，解决方案是为始终为空的列分配列别名。
select * from (select D.dept_no, D.comment
from dept D
union
select R.dept_no, NULL "nullable_comment"
from redundant_dept R
)
The alias does not have to be the same as the corresponding column, but the column heading in the result is driven by the first query from among the union members, so it's probably a good practice.
别名不必与对应的列相同，但结果中的列标题由联合成员中的第一个查询驱动，因此这可能是一个很好的做法。
