我有太多选择语句的查询。因此，我使用了条件聚合。该查询引用了SQL Server 2008中Instock表中的数据，最终查询如下所示：
select
SUM(CASE WHEN MONTH = '2013-8-1' THEN CurrentStock ELSE 0 END) as CurrentStock,
Isnull(Sum(CASE WHEN MONTH = '2014-2-1' THEN CPU ELSE 0 END), 0) - Isnull(Sum(CASE WHEN MONTH = '2013-8-1' THEN CPU ELSE 0 END), 0) as CPU,
SUM(CASE WHEN Month = '2013-8-1' THEN NonCPUWIP ELSE 0 END) as NonCPU,
Isnull(Sum(CASE WHEN MONTH = '2014-2-1' THEN ScrapDiscarded ELSE 0 END), 0) - Isnull(Sum(CASE WHEN MONTH = '2013-8-1' THEN ScrapDiscarded ELSE 0 END), 0) as Scrap,
SUM(CASE WHEN Month = '2013-8-1' THEN WaitingForApproval ELSE 0 END) as WFA
from
Instock
where
Vendor = 'abc'
问题是需要包括的最后一个查询。它从OutStock表中获取数据，但我想在上一个查询中包含该查询，以便它以单行的形式返回结果。我无法使用嵌套查询来满足要求，但是我希望使用条件聚合来获得相同的结果以提高数据库性能。
需要包括的查询是：
select
COUNT(Product) as Despatched
from
OutStock
where
DispatchDate between '2013-8-1' and '2014-2-1'
and Vendor = 'abc'`
所以结合两者，我想作为一个查询执行，它将返回一行
只需将第二个查询添加到第一个查询的列列表中，因为它将仅返回一行。试试这个。
SELECT Sum(CASE
WHEN MONTH = '2013-8-1' THEN CurrentStock
ELSE 0
END) AS CurrentStock,
Isnull(Sum(CASE WHEN MONTH = '2014-2-1' THEN CPU ELSE 0 END), 0) -
Isnull(Sum(CASE WHEN MONTH = '2013-8-1' THEN CPU ELSE 0 END), 0) AS CPU,
Sum(CASE WHEN Month = '2013-8-1' THEN NonCPUWIP ELSE 0 END) AS NonCPU,
Isnull(Sum(CASE WHEN MONTH = '2014-2-1' THEN ScrapDiscarded ELSE 0 END), 0) -
Isnull(Sum(CASE WHEN MONTH = '2013-8-1' THEN ScrapDiscarded ELSE 0 END), 0)AS Scrap,
Sum(CASE WHEN Month = '2013-8-1' THEN WaitingForApproval ELSE 0 END) AS WFA,
(SELECT Count(STBSerialNUMBER)
FROM OutStock
WHERE DispatchDate BETWEEN '2013-8-1' AND '2014-2-1'
AND Vendor = 'abc' ) as Despatched
FROM Instock I
WHERE Vendor = 'abc'
更新 ：
如果您希望每个模型的结果计数，则将其转换subquery为correlated subquery。应该是这样的
(SELECT Count(STBSerialNUMBER)
FROM OutStock O
WHERE o.model = i.model -- here is the change
AND O.DispatchDate BETWEEN '2013-8-1' AND '2014-2-1'
AND o.Vendor = 'abc')
这里每一个model在Instock表中的相应count的outstock将caluclated。
或者您也可以使用Left Join。首先计算出每个的计数model在Outstock表中join的结果返回给Instock使用模型表
....
From Instock I Left Join
(SELECT Count(STBSerialNUMBER) OutStock_count,Model
FROM OutStock O
WHERE O.DispatchDate BETWEEN '2013-8-1' AND '2014-2-1'
AND o.Vendor = 'abc'
Group by Model) O on o.model = i.model
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句