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

Question

SELECT Name AS "Avatar Name", 
       COUNT(WEAPON_ID) AS "Active Weapons Held |", 
       SUM(W.COST) AS "Total Weapons Cost"
FROM   Avatar A, Weapon_Held H, Weapon W
WHERE  A.Avatar_ID=H.AVATAR_ID AND H.WEAPON_ID=W.WEAPON_ID 
Group by Name;

Answer 1

首先，切勿在FROM子句中使用逗号。 始终使用正确、明确的JOIN语法。

其次，使用表别名，但在任何地方都使用它们：

SELECT Name AS "Avatar Name", 
       COUNT(H.WEAPON_ID) AS "Active Weapons Held", 
       SUM(W.COST) AS "Total Weapons Cost"
FROM Avatar A JOIN
     Weapon_Held H 
     ON A.Avatar_ID = H.AVATAR_ID JOIN
     Weapon W
     ON H.WEAPON_ID = W.WEAPON_ID 
Group by Name;

Answer 2

您收到错误是因为查询中有多个具有相同名称的表，并且它不知道从何处提取它。 尝试将查询更改为以下内容：

SELECT  A.Name AS "Avatar Name", 
        COUNT(H.WEAPON_ID) AS "Active Weapons Held |", 
        SUM(W.COST) AS "Total Weapons Cost"
FROM    Avatar      A
JOIN    Weapon_Held H   ON  A.Avatar_ID=H.AVATAR_ID
JOIN    Weapon      W   ON  H.WEAPON_ID=W.WEAPON_ID 
Group by A.Name;

Answer 3

这些表中是否有两个或多个名为"名称"的列？请使用隐式名称，例如："A.Name"或"H.Name"，例如：

Select A.Name....
Group by A.Name