如何在多个条件下应用where子句

qojgxg4l 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(273)

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个items:-
找出仅由设备A和/或设备B使用的所有计算机零件号
如果计算机零件号由设备A和设备B以外的设备使用，则过滤掉查询结果。
如果computerpartnumber同时被equipmenta和equipmentc使用，则也过滤掉结果。
但是，无法成功筛选出项3。为了达到第3项我应该做什么？附加了表和查询快照。提前谢谢！
table
查询

sql ms-access

来源：https://stackoverflow.com/questions/62081459/ms-access-sql-how-to-apply-where-clause-in-multiple-conditions

2条答案

按热度按时间

pftdvrlh1#

您需要做的是检查一个部件在所有设备中使用的总次数是否等于一个部件被设备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"))

当做，

赞(0）回复(0）举报 2021-08-13

xqkwcwgp2#

你可以用 not exists :

select s.*
from stock as s
where not exists (select 1
                  from stock as s2
                  where s2.ComputerPartNumber = s.ComputerPartNumber and
                        s2.EquipmentName not in ("EquipmentA", "EquipmentB")
                 );

赞(0）回复(0）举报 2021-08-13

我来回答

如何在多个条件下应用where子句

2条答案

相关问题

热门标签

最新问答