SQL执行报错：Ambiguous column name 'Chem_Num' 该如何解决？

错误原因 #

你的SQL语句里，Chem_Num字段同时存在于CHEMICAL、CARRIED和USED三个表中。当你在SELECT、WHERE和ORDER BY里直接写Chem_Num时，数据库无法判断你要引用哪个表的该字段，因此抛出“Ambiguous column name 'Chem_Num'”错误。

解决方法 #

明确指定每个Chem_Num字段所属的表，最简单的方式是给表起短别名，或者直接用完整表名。下面是修改后的代码：

SELECT 
    C.Chem_Num, C.Chem_Retail_Name, 
    CA.Carried_Num as "Item Carry", 
    T.Tech_LName as "Driver",
    U.Job_Num, U.Used_Qty AS "Used on Job", 
    (CA.Carried_Outbound - CA.Carried_Return) AS "Gone from Truck" 
FROM
    PEPS.CHEMICAL C
JOIN 
    PEPS.CARRIED CA ON C.Chem_Num = CA.Chem_Num
JOIN 
    PEPS.ASSIGNMENT A ON CA.Assign_Num = A.Assign_Num
JOIN 
    PEPS.TECHNICIAN T ON A.Tech_Num = T.Tech_Num
JOIN 
    PEPS.USED U ON C.Chem_Num = U.Chem_Num
WHERE
    C.Chem_Num IN (101, 103, 107) 
ORDER BY 
    C.Chem_Num ASC, CA.Carried_Num ASC;

修改说明 #

• 给每个表设置了简短别名：CHEMICAL→C，CARRIED→CA，ASSIGNMENT→A，TECHNICIAN→T，USED→U，让代码更简洁易读
• 所有涉及Chem_Num的地方都明确指定了表别名（这里选择了CHEMICAL表的Chem_Num，你可以根据实际业务需求调整为其他表的，只要保持一致即可）
• 其他字段如Carried_Num也通过别名明确了来源，避免后续出现类似的模糊字段错误

内容的提问来源于stack exchange，提问作者Matthew Morgan