Where子句中的SQL计数
Where子句中的SQL计数
提问于 2013-08-28 20:43:00
我正在努力寻找在报告中出现过一次或更少的项目。我知道，要找出每件物品有多少次，我就用这个。
select COUNT(VP.VendorPartID)
from Purchasing.PurchaseOrder PO with (nolock)
inner join dbo.tblVendor V with (nolock)
on PO.VendorID=V.VendorID
inner join Purchasing.PurchaseOrderItem POI with (nolock)
on PO.PurchaseOrderID=POI.PurchaseOrderID
inner join Purchasing.VendorPart VP with (nolock)
on POI.VendorPartID=VP.VendorPartID
where V.ProductTypeID=4
group by PO.PurchaseOrderID但是我尝试将它嵌套到另一个查询中，以便能够设置它必须出现一次或更短的时间，并且它说有一个错误，因为
“子查询返回的值超过一个。当子查询跟随=，!=，<，<=，>，>=或子查询用作表达式时，这是不允许的。”
我这么做了，我猜这很可能是错的，哈哈。
select VP.VendorPartID,VP.VendorPartDescription
from Purchasing.PurchaseOrder PO with (nolock)
inner join dbo.tblVendor V with (nolock)
on PO.VendorID=V.VendorID
inner join Purchasing.PurchaseOrderItem POI with (nolock)
on PO.PurchaseOrderID=POI.PurchaseOrderID
inner join Purchasing.VendorPart VP with (nolock)
on POI.VendorPartID=VP.VendorPartID
where (
select COUNT(VP.VendorPartID)
from Purchasing.PurchaseOrder PO with (nolock)
inner join dbo.tblVendor V with (nolock)
on PO.VendorID=V.VendorID
inner join Purchasing.PurchaseOrderItem POI with (nolock)
on PO.PurchaseOrderID=POI.PurchaseOrderID
inner join Purchasing.VendorPart VP with (nolock)
on POI.VendorPartID=VP.VendorPartID
where V.ProductTypeID=4
group by PO.PurchaseOrderID
) < 2
group by VP.VendorPartID,VP.VendorPartDescription期望的结果是
VendorPartID VendorPartDescription
001 name 1
002 name 2
003 name 3 它将只显示那些在订购单上出现过一次的。
回答 3
Stack Overflow用户
发布于 2013-08-28 20:48:34
HAVING子句是您所需要的--它类似于WHERE子句，但应用于GROUP BY
具有以下效果的事物：
Select Id, count(othercolumn)
from sometable
where somecolumn = something
group by Id
having (count(somecolumn) < 2)Stack Overflow用户
发布于 2013-08-28 20:45:44
如果这真的适用于你的计数：
select COUNT(VP.VendorPartID)
from Purchasing.PurchaseOrder PO with (nolock)
inner join dbo.tblVendor V with (nolock)
on PO.VendorID=V.VendorID
inner join Purchasing.PurchaseOrderItem POI with (nolock)
on PO.PurchaseOrderID=POI.PurchaseOrderID
inner join Purchasing.VendorPart VP with (nolock)
on POI.VendorPartID=VP.VendorPartID
where V.ProductTypeID=4
group by PO.PurchaseOrderID`然后简单地添加一个having子句：
select PO.PurchaseOrderID, COUNT(VP.VendorPartID)
from Purchasing.PurchaseOrder PO with (nolock)
inner join dbo.tblVendor V with (nolock)
on PO.VendorID=V.VendorID
inner join Purchasing.PurchaseOrderItem POI with (nolock)
on PO.PurchaseOrderID=POI.PurchaseOrderID
inner join Purchasing.VendorPart VP with (nolock)
on POI.VendorPartID=VP.VendorPartID
where V.ProductTypeID=4
group by PO.PurchaseOrderID
having COUNT(VP.VendorPartID) <= 1Stack Overflow用户
发布于 2013-08-28 20:48:43
试试这个，它允许您根据计数获得详细信息。
select * from
(select VP.VendorPartID,VP.VendorPartDescription,COUNT(VP.VendorPartID) as PartCount
from Purchasing.PurchaseOrder PO with (nolock)
inner join dbo.tblVendor V with (nolock)
on PO.VendorID=V.VendorID
inner join Purchasing.PurchaseOrderItem POI with (nolock)
on PO.PurchaseOrderID=POI.PurchaseOrderID
inner join Purchasing.VendorPart VP with (nolock)
on POI.VendorPartID=VP.VendorPartID
group by
VP.VendorPartID,VP.VendorPartDescription) as qCounts
where qCounts.PartCount < 2页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18497832
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号