SQL查询已引用两表AccountNo，为何仍报Ambiguous column name列名歧义错误？

错误原因 #

你收到这个报错的核心原因是，AccountNo 字段同时存在于 InvoiceLineItems 和 GLAccounts 两张表中，哪怕你在JOIN关联逻辑中已经保证了两个表的AccountNo值一致，SQL查询引擎也不会自动推断你SELECT子句里的AccountNo要取哪张表的，必须手动明确指定字段所属的表。

修复方案 #

可以直接给SELECT里的AccountNo加上表名前缀，也可以给表起别名简化写法，以下是修改后的代码示例：

SELECT VendorName, VendorState, InvoiceNumber, InvoiceTotal, InvoiceLineItems.AccountNo,
       InvoicelineItemDescription, AccountDescription
FROM   Vendors JOIN Invoices
    ON Vendors.VendorID = Invoices.VendorID
JOIN InvoiceLineItems
    ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
JOIN GLAccounts
    ON InvoiceLineItems.AccountNo = GLAccounts.AccountNo
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
ORDER BY VendorName;

如果想简化写法可以给表加别名，代码可读性会更高：

SELECT v.VendorName, v.VendorState, i.InvoiceNumber, i.InvoiceTotal, ili.AccountNo,
       ili.InvoicelineItemDescription, gla.AccountDescription
FROM   Vendors v JOIN Invoices i
    ON v.VendorID = i.VendorID
JOIN InvoiceLineItems ili
    ON i.InvoiceID = ili.InvoiceID
JOIN GLAccounts gla
    ON ili.AccountNo = gla.AccountNo
WHERE i.InvoiceTotal - i.PaymentTotal - i.CreditTotal > 0
ORDER BY v.VendorName;

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