时间戳

首页 > 解决方案 > MS-ACCESS / SQL - 如何在多个条件下应用 where 子句

sql - MS-ACCESS / SQL - 如何在多个条件下应用 where 子句

问题描述

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 项：-

  1. 找出仅由 EquipmentA 和/或 EquipmentB 使用的所有 ComputerPartNumber
  2. 如果ComputerPartNumber被EquipmentA和EquipmentB以外的设备使用，则过滤掉查询结果。
  3. 如果 EquipmentA 和 EquipmentC 都使用 ComputerPartNumber，则也过滤掉结果。

但是无法成功过滤出第 3 项。为了实现item3，我应该怎么做？附加了表和查询快照。提前致谢！

桌子

询问

标签： sqlms-access

解决方案

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

问候，

推荐阅读