如何获取多个条件的子查询中的总行数 [英] How to get total number of rows within subquery for multiple conditions

问题描述

此查询返回的行数，其中产品数量之和"等于0并且产品名称"为"XYZ".但是我希望每种产品都具有相同的结果.例如，如果我从where子句中删除产品名称，那么它将带来数量为0的每个产品的SUM.我希望它针对每个产品分别返回.产品将来可能会增加.我必须首先检查CustomerProduct表中有多少个不同的产品，然后需要为每个产品查询相同的内容.我怎样才能做到这一点.标题可能不是完美的，如果需要，请建议/更正.

This query return number of rows where Sum of Product's Quantity is equal to 0 and Product Name is 'XYZ'. But I want same result for each products. For example if I remove product name from where clause so it will bring SUM of every product where quantity is 0. I want it to return separately for each products. Products may increase in future. I have to first check how many distinct products are there in CustomerProduct table and then need to query same things for each product. How can I do that. Title might not is perfect, Please suggest/correct if it required to.

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 = 'XYZ'
GROUP BY VI.Name, Cp.ProductName, CP.ProductQuantity
HAVING  SUM( CP.ProductQuantity ) = 0) as x

推荐答案

阿凡(Affan)，您可以添加tsql脚本来创建包含一些数据的表，以便我们更轻松地提供帮助.但是，如果您简化查询，则可以使每个产品和供应商的ProductQuantity为零

Affan, can you add tsql script to create the tables with some data, so it is easier for us to assist. However if you simplify the query then you can get each product and vendor with zero ProductQuantity

select  VI.Name, Cp.ProductName, SUM( CP.ProductQuantity ) 
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' 
GROUP BY VI.Name, Cp.ProductName
HAVING  SUM( CP.ProductQuantity ) = 0

这篇关于如何获取多个条件的子查询中的总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！