sql - SQL 中列名不明确的查询错误

Question

此查询 (InvoiceID) 出现不明确的列名错误。我不明白为什么。它们似乎都已正确连接，那么为什么 SSMS 不知道显示 VendorID？
询问:

SELECT 
    VendorName, InvoiceID, InvoiceSequence, InvoiceLineItemAmount
FROM Vendors 
JOIN Invoices ON (Vendors.VendorID = Invoices.VendorID)
JOIN InvoiceLineItems ON (Invoices.InvoiceID = InvoiceLineItems.InvoiceID)
WHERE  
    Invoices.InvoiceID IN
        (SELECT InvoiceSequence 
         FROM InvoiceLineItems
         WHERE InvoiceSequence > 1)
ORDER BY 
    VendorName, InvoiceID, InvoiceSequence, InvoiceLineItemAmount

Answer 1

当我们从 中选择数据时，我们会遇到这个错误。多 table 通过连接表和 所选列中至少有一个 (使用 * 选择所有列时也会发生)在多个表中存在同名 (我们选择/连接的表)。在这种情况下，我们必须指定从哪个表中选择列。
以下是上述概念的示例解决方案实现
我觉得你只有InvoiceID有歧义两者都存在于 InvoiceLineItems和 Invoices其他领域似乎不同。所以试试这个
我只是用 Invoices.InvoiceID 替换 InvoiceID

   SELECT 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
    FROM Vendors 
    JOIN Invoices ON (Vendors.VendorID = Invoices.VendorID)
    JOIN InvoiceLineItems ON (Invoices.InvoiceID = InvoiceLineItems.InvoiceID)
    WHERE  
        Invoices.InvoiceID IN
            (SELECT InvoiceSequence 
             FROM InvoiceLineItems
             WHERE InvoiceSequence > 1)
    ORDER BY 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount

您可以将 tablename.columnnae 用于所有列(in selection、where、group by 和 order by)，而无需使用任何别名。但是，您可以按照其他答案的指导使用别名