ORA-00918:列在SELECT中含糊不清* [英] ORA-00918: column ambiguously defined in SELECT *

问题描述

获取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.

这篇关于ORA-00918:列在SELECT中含糊不清*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！