HAVING和WHERE，SQL Server之间的混淆 [英] Confusion between HAVING and WHERE, SQL Server

问题描述

我正在尝试获取所有产品数量(SUM)等于零的供应商.到目前为止，这是我所做的，但不返回任何行，也不给出任何错误.我确保有数量为零的产品，并且脚本正在正确的数据库上运行.

I am trying to fetch all vendors whose product's quantity (SUM) is equal to zero. This is what I have done so far but it doesn't return any row nor it gives any error. I am making sure there are products whose quantity is zero and script is running on proper database.

select 
    VI.Name, Cp.ProductName
from
    VendorInfo VI 
inner join 
    VendorTrading VT on VI.Id = VT.VendorId 
inner join 
    CustomerProducts CP on VT.Id = CP.VendorTradingId
group by 
    VI.Name, CP.ProductName
having
    sum(CP.ProductQuantity) = 0

我对HAVING和WHERE感到困惑.我该怎么办?

I am confused about HAVING and WHERE. What do I do?

更新:

这是VendorInfo表:

这是VendorTrading表:

这是CustomerProduct表:

现在的预期结果应该是:

Now expected result result should be like:

同一产品将有多行，但交易日期不同，因此应首先将所有产品数量求和，如果等于零，则返回VendorName和产品名称，在一定时间内其产品数量为0.稍后，我还需要产品数量为0的供应商总数. 希望它能清除一切

There will be multiple rows of same product but with different Tradedate, So it should first SUM all Product Quantity and if it equals to zero then it returns VendorName and Product name that it has 0 Product quantity in certain period of time. later I also need total count of Vendor whose Product Quantity is 0. Hope it clears everything

推荐答案

感谢大家的辛苦，我终于做到了.

Thanks every one for your efforts, I finally did it this way.

select  Count(*) From 
(select distinct VI.Name, Cp.ProductName
FROM VendorInfo VI inner join VendorTrading VT on VI.Id = VT.VendorId inner join CustomerProducts CP on VT.Id = CP.VendorTradingId
Where VT.Tradedate = '2015-12-25' and CP.ProductName = 'ULTRA'
GROUP BY VI.Name, Cp.ProductName, CP.ProductQuantity
HAVING  COUNT( CP.ProductQuantity ) = 0) as x

但这是另一个问题，我将其发布为新问题.请参见此处.谢谢

but here is another problem, I posted it as new question. see here. Thanks

这篇关于HAVING和WHERE，SQL Server之间的混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！