我得到的错误是'Msg 8120，'Vendors.VendorName‘在选择列表中无效’

Question

select 
    VendorID, 
    VendorName, 
    VendorCity, 
    VendorState, 
    VendorPhone, 
    concat(VendorContactFName, ' ',VendorContactFName) AS VendorContactFullName, 
    max(InvoiceDueDate)
from 
    Vendors v 
inner join 
    InvoiceArchive IVA on v.VendorID = IVA.VendorID
where 
    VendorCity in ('California', 'Arizona', 'Nevada');

Answer 1

您似乎在寻找一个聚合查询。这意味着您需要在group by子句中列出所有非聚集列。这看起来像是：

select 
    v.VendorID, 
    v.VendorName, 
    v.VendorCity, 
    v.VendorState, 
    v.VendorPhone, 
    concat(v.VendorContactFName, ' ', v.VendorContactFName) AS VendorContactFullName, 
    max(iv.InvoiceDueDate) 
from Vendors v 
inner join InvoiceArchive IVA on v.VendorID = IVA.VendorID 
where v.VendorCity in ('California','Arizona','Nevada')
group by 
    v.VendorID, 
    v.VendorName, 
    v.VendorCity, 
    v.VendorState, 
    v.VendorPhone,
    v.VendorContactFName, 
    v.VendorContactFName

请注意，在此查询中，所有列名都使用它们所属的表进行限定。

您可以使用cross apply获得相同的结果，它不需要聚合：

select 
    v.VendorID, 
    v.VendorName, 
    v.VendorCity, 
    v.VendorState, 
    v.VendorPhone, 
    concat(v.VendorContactFName, ' ', v.VendorContactFName) AS VendorContactFullName, 
    iv.MaxInvoiceDueDate
from Vendors v
cross apply (
    select max(iva.InvoiceDueDate) MaxInvoiceDueDate
    from InvoiceArchive iva 
    where iva.VendorID = v.VendorID 
) iva