ORA-00918: 列在 SELECT * 中定义不明确

2021-12-05 00:00:00 sql oracle ora-00918

获取 ORA-00918:定义不明确的列:运行此 SQL:

Getting ORA-00918: column ambiguously defined: running this 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

请问有什么建议吗?

推荐答案

一个查询的投影只能有一个给定名称的实例.正如您的 WHERE 子句所示，您有多个表，其中有一列名为 ID.因为您选择了 *，所以您的投影将有多个名为 ID 的列.或者，如果不是编译器抛出 ORA-00918，它就会发生.

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.

解决方案非常简单:您必须扩展投影以显式选择命名列.然后你可以省略重复的列，只保留(比如)COACHES.ID 或使用列别名:coaches.id as COACHES_ID.

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.

也许这会让您觉得打字很多，但这是唯一的方法.如果方便的话， SELECT * 在生产代码中被认为是不好的做法:显式命名的列更安全.

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.

相关文章