只有在表中没有其他相关项时才获取sum
只有在表中没有其他相关项时才获取sum
提问于 2014-06-19 14:43:42
我在Server 2005中有以下查询返回的结果。现在，我想加入一个商业规则。如果部件号有一个与之相关的工厂，并且它不是我目前没有检查的工厂，那么它应该被列为零。
在下面的结果中，对于xample，工厂部分编号组合96-ABC应该将和返回为零。但是11-DEF的组合应该继续显示为50.所有其他值都应该返回与现在相同的结果。
如何修改此查询？虽然我试了几个小时，但仍未找到解决办法。
解释性：对于下面突出显示的行，部分编号是ABC。但该部分与工厂"96“没有任何关系，因为您可以在MHL和MMHP表…中看到数据。(这类似于DEF场景)…。但零件号与其他plants (20和21)有关联，因此部件有restricted association，它应该显示为0，用于工厂"96"…。。而对于DEF部分，它是包含所有工厂的OPEN association -因此所有行项的总和应该是SumQty (即，现在显示的50 )。
注意:我们可以在一个查询中得到它吗？分割成两个查询并进行更新对我来说是行不通的。
电流结果
数据
DECLARE @MHL TABLE (LineNumber VarCHAR(5), PartNumber VARCHAR(10), Qty INT)
INSERT INTO @MHL VALUES ('10001','ABC',10)
INSERT INTO @MHL VALUES ('10002','ABC',100)
INSERT INTO @MHL VALUES ('10003','DEF',50)
INSERT INTO @MHL VALUES ('10005','KXY',25)
INSERT INTO @MHL VALUES ('10006','KXY',30)
DECLARE @MHP TABLE (PlantCode VarCHAR(5), LineNumber VARCHAR(5))
INSERT INTO @MHP VALUES ('20','10001')
INSERT INTO @MHP VALUES ('21','10002')
INSERT INTO @MHP VALUES ('80','10005')
INSERT INTO @MHP VALUES ('80','10006')
DECLARE @MasterPLantParts TABLE (PlantCode VarCHAR(5), PartNumber VARCHAR(10))
INSERT INTO @MasterPLantParts VALUES ('20','ABC')
INSERT INTO @MasterPLantParts VALUES ('21','ABC')
INSERT INTO @MasterPLantParts VALUES ('96','ABC')
INSERT INTO @MasterPLantParts VALUES ('11','DEF')
INSERT INTO @MasterPLantParts VALUES ('80','KXY')选择查询
select
mpp.PlantCode PlantCode,
mpp.PartNumber PartNumber,
sum(MHL.Qty) as SumQty
from @MasterPLantParts mpp
left outer join @MHP MHP
on mpp.PlantCode = MHP.PlantCode
inner join @MHL MHL
on (
MHL.LineNumber = MHP.LineNumber
or (
mpp.PartNumber = MHL.PartNumber
and MHP.LineNumber is null
)
)
group by mpp.PlantCode, mpp.PartNumber
order by mpp.PlantCode, mpp.PartNumber回答 3
Stack Overflow用户
发布于 2014-06-19 16:26:15
我想这可能是你想要的，你需要分步骤.
select mpp.PlantCode PlantCode, mpp.PartNumber PartNumber, sum(IsNull(mhl.Qty,0)) as Qty
into #tt
from @MasterPLantParts mpp
left join @MHP MHP on mpp.PlantCode = MHP.PlantCode
left join @MHL mhl on (mhl.PartNumber = mpp.PartNumber and mhl.LineNumber = mhp.LineNumber)
group by mpp.PlantCode, mpp.PartNumber
order by mpp.PlantCode, mpp.PartNumber
update tt
set Qty = mhl.Qty
FROM #tt tt
inner join @MHL mhl on (mhl.PartNumber = tt.PartNumber)
where tt.Qty=0
and not exists (select 1 from @MasterPLantParts mpp where mpp.PlantCode<>tt.PlantCode and mpp.PartNumber=tt.PartNumber)
select * from #tt
drop table #tt这会产生..。
PlantCode PartNumber SumQty
20 ABC 10
21 ABC 100
96 ABC 0
11 DEF 50
80 KXY 55作为一个select语句，可以这样做.
select mpp.PlantCode PlantCode, mpp.PartNumber PartNumber, Sum(case mhl2.RecCount
when 1 then mhl2.Qty
ELSE IsNull(mhl.Qty,0) END) as Qty
from @MasterPLantParts mpp
left join @MHP MHP on mpp.PlantCode = MHP.PlantCode
left join @MHL mhl on (mhl.PartNumber = mpp.PartNumber and mhl.LineNumber = mhp.LineNumber)
left join (select PartNumber, count(1) RecCount, Sum(Qty) as Qty
from @MHL
group by PartNumber
having count(1)=1) mhl2 on (mhl2.PartNumber = mpp.PartNumber)
group by mpp.PlantCode, mpp.PartNumberStack Overflow用户
发布于 2014-06-20 07:16:55
下面是一个查询。我改变了这方面的逻辑。
首先，我从MHP和MHL表中收集数据，计数数据的总和。
然后加入他们。
;WITH twoInOne AS (
SELECT MHP.PlantCode, MHL.PartNumber, SUM(MHL.Qty) AS Qty
FROM @MHL AS MHL
LEFT JOIN @MHP AS MHP
ON MHL.LineNumber = MHP.LineNumber
GROUP BY MHP.PlantCode, MHL.PartNumber
)
SELECT
mpp.PlantCode PlantCode,
mpp.PartNumber PartNumber,
CASE
WHEN tIO.PlantCode IS NOT NULL THEN tIO.Qty
WHEN tIO2.PlantCode IS NULL AND tIO2.PartNumber IS NOT NULL THEN tIO2.Qty
ELSE 0
END
FROM @MasterPLantParts mpp
LEFT JOIN twoInOne AS tIO
ON mpp.PlantCode = tIO.PlantCode
AND tIO.PlantCode IS NOT NULL
LEFT JOIN twoInOne AS tIO2
ON mpp.PartNumber = tIO2.PartNumber
AND tIO2.PlantCode IS NULL
order by mpp.PlantCode, mpp.PartNumber我不认识甲骨文，但我想可能是这样的：
SELECT
mpp.PlantCode PlantCode,
mpp.PartNumber PartNumber,
CASE
WHEN tIO.PlantCode IS NOT NULL THEN tIO.Qty
WHEN tIO2.PlantCode IS NULL AND tIO2.PartNumber IS NOT NULL THEN tIO2.Qty
ELSE 0
END
FROM @MasterPLantParts mpp,
(SELECT MHP.PlantCode, MHL.PartNumber, SUM(MHL.Qty) AS Qty
FROM @MHL MHL, @MHP MHP
WHERE MHL.LineNumber = MHP.LineNumber(+)
GROUP BY MHP.PlantCode, MHL.PartNumber
) tIO,
(SELECT MHP.PlantCode, MHL.PartNumber, SUM(MHL.Qty) AS Qty
FROM @MHL MHL, @MHP MHP
WHERE MHL.LineNumber = MHP.LineNumber(+)
GROUP BY MHP.PlantCode, MHL.PartNumber
) tIO2
WHERE mpp.PlantCode = tIO.PlantCode(+)
AND mpp.PartNumber = tIO2.PartNumber(+)
AND tIO.PlantCode IS NOT NULL
AND tIO2.PlantCode IS NULL
order by mpp.PlantCode, mpp.PartNumberStack Overflow用户
发布于 2014-06-19 16:49:25
警告:未经测试，也可能没有效率- sqlfiddle不起作用。
select mpp.PlantCode PlantCode,
mpp.PartNumber PartNumber,
nvl(sum(MHL.Qty),0) as SumQty -- edit1: add nvl
from @MasterPLantParts mpp
left outer join @MHP MHP on mpp.PlantCode = MHP.PlantCode
left join @MHL MHL on ( -- edit2: from inner join to left join
MHL.LineNumber = MHP.LineNumber
or (
mpp.PartNumber = MHL.PartNumber
and mpp.PartNumber not in (
select MHL2.PartNumber
from @MHL MHL2
inner join @MHP mhp2 on MHL2.LineNumber = MHP2.LineNumber
where mhp2.PlantCode != mpp.PlantCode
)
)
)
group by mpp.PlantCode, mpp.PartNumber
order by mpp.PlantCode, mpp.PartNumber页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24309409
复制相关文章
相似问题
腾讯云开发者
