ORA-00918：列在SELECT中含糊*

Question

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

有什么建议吗？

Answer 1

在选择对应列可以为空的联合时，您还会看到此错误。select * from (select D.dept_no, D.nullable_comment&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from dept D&nbsp; &nbsp; &nbsp; &nbsp;union&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;select R.dept_no, NULL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from redundant_dept R)这显然使解析器感到困惑，一种解决方案是将列别名分配给始终为null的列。select * from (select D.dept_no, D.comment&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from dept D&nbsp; &nbsp; &nbsp; &nbsp;union&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;select R.dept_no, NULL "nullable_comment"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from redundant_dept R)别名不必与对应的列相同，但是结果中的列标题是由并集成员中的第一个查询驱动的，因此这可能是一个好习惯。