SQL 中列名不明确的查询错误 [英] Query error with ambiguous column name in SQL

问题描述

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

I get an ambiguous column name error with this query (InvoiceID). I can't figure out why. They all seem to be joined correctly so why doesn't SSMS know to display 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

推荐答案

当我们从多个表中选择数据时，我们会遇到这个错误选择的列(使用 * 选择所有列时也会发生这种情况)在多个表中存在相同的名称(我们的选择/连接表).在这种情况下，我们必须指定从哪个表中选择列.

We face this error when we are selecting data from more than one tables by joining tables and at least one of the selected columns (it will also happen when use * to select all columns) exist with same name in more than one tables (our selected/joined tables). In that case we must have to specify from which table we are selecting out column.

以下是上述概念的示例解决方案实现

我认为您只有在 InvoiceLineItems 和 Invoices 中都存在的 InvoiceID 中存在歧义，其他字段似乎不同.所以试试这个

I think you have ambiguity only in InvoiceID that exists both in InvoiceLineItems and Invoices Other fields seem distinct. So try This

我只是用 Invoices.InvoiceID 替换了 InvoiceID

I just replace InvoiceID with Invoices.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)，而无需使用任何别名.但是，您可以按照其他答案的指导使用别名

You can use tablename.columnnae for all columns (in selection,where,group by and order by) without using any alias. However you can use an alias as guided by other answers

这篇关于SQL 中列名不明确的查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！