SELECT Stock.*
FROM Stock
WHERE (
(
(Stock.ComputerPartNumber) In (SELECT [ComputerPartNumber] FROM [Stock] As Tmp GROUP BY [ComputerPartNumber] HAVING Count(*)=2)
)
AND
(
(Stock.EquipmentName)="EquipmentA" Or (Stock.EquipmentName)="EquipmentB")
)
OR (
(
(Stock.ComputerPartNumber) In (SELECT [ComputerPartNumber] FROM [Stock] As Tmp GROUP BY [ComputerPartNumber] HAVING Count(*)=1)
)
AND (
(Stock.EquipmentName)="EquipmentA" Or (Stock.EquipmentName)="EquipmentB"
)
);
我正在使用上面的SQL实现以下3个项目:-
但是,项目3无法成功滤出。我应该怎么做才能达到item3?表和查询快照已附加。提前致谢!
您需要做的是检查零件在所有设备中的使用总次数是否等于设备A或B使用零件的总次数:
SELECT S.StorageID, S.ComputerPartNumber, S.EquipmentName, S.Result
FROM Stock AS S
WHERE
(SELECT COUNT(*) FROM Stock AS S1 WHERE S1.ComputerPartNumber=S.ComputerPartNumber)
=(SELECT COUNT(*) FROM Stock AS S2 WHERE S2.ComputerPartNumber=S.ComputerPartNumber AND S2.EquipmentName IN("EquipmentA","EquipmentB"))
问候,
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句