时间戳

首页 > 解决方案 > 我得到的错误是'Msg 8120，'Vendors.VendorName' 在选择列表中无效'

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

问题描述

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');

标签： sqlsql-servertsqlgroup-bymax

解决方案

您似乎正在寻找一个聚合查询。这意味着您需要在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

推荐阅读