如何解决 Oracle 的 ORA-00918: SELECT * 查询中的模糊列定义错误？

*Oracle ORA-00918：不明确的列定义 – SELECT 查询问题**

对 Oracle 中的多个连接表运行 SELECT * 查询可能会导致可怕的 ORA-00918 错误：“列定义不明确”。当您的查询包含来自不同表的具有相同名称的列时，就会发生这种情况。 Oracle 无法确定您要检索哪一列。

这是一个经常触发此错误的示例查询：

<code class="language-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</code>

问题的根源

SELECT * 尝试检索所有列，但由于 id 存在于多个表中（coaches、people、users），Oracle 不确定要选择哪个 id 列。

解决方案：精确的列规范

要修复 ORA-00918，请避免 SELECT *。 相反，明确列出您需要的列。 有两种主要方法可以实现这一目标：

1。直接列选择

使用其完全限定名称 (table.column) 指定每列：

<code class="language-sql">SELECT coaches.id,
       people.name,
       users.username,
       coaches.team
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</code>

2。使用列别名

使用 AS 关键字为同名的列分配唯一的别名：

<code class="language-sql">SELECT coaches.id AS coaches_id,
       people.name AS person_name,
       users.username AS user_username,
       coaches.team AS coaches_team
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</code>

通过使用任一方法，您都可以向 Oracle 提供明确的指令，解决 ORA-00918 错误并确保查询成功执行。 请记住将示例列名称替换为您的实际列名称。

以上是如何解决 Oracle 的 ORA-00918: SELECT * 查询中的模糊列定义错误？的详细内容。更多信息请关注PHP中文网其他相关文章！