如何在只有所有子查询为true的情况下返回匹配的列值

Question

我想调整下面的SQL语句，使其只返回满足所有SELECT PART_ID FROM FABRICATION...条件的PART_ID。

目前，下面的语句将返回part_id的1&2，当意图仅返回1时。

谢谢你的建议。

SELECT * 
FROM Part 
WHERE Section = 'C6x1_5/8x1/4' 
AND Length BETWEEN 41.99 AND 42.01 
AND Part.Part_ID IN (SELECT Part_ID FROM Fabrication 
WHERE Type LIKE 'HOLE' 
AND Face = 1 
AND Side = 3 
AND Location BETWEEN 2.99 AND 3.01 
AND Offset = 2 
AND Parameter_1 = 0.5625)
AND Part.Part_ID IN (SELECT Part_ID FROM Fabrication 
WHERE Type LIKE 'HOLE' 
AND Face = 1 
AND Side = 3 
AND Location BETWEEN 5.99 AND 6.01 
AND Offset = 2 
AND Parameter_1 = 0.5625)

制造台：

侧面偏移参数_2>参数_30.56250><1><2>0<1>600<1>00<1>900

PART_ID	类型	面	位置	参数_1
1	孔	1			3	6	2	0
1	孔	3		0.5625	0
2	孔	3	2	0.5625
2	孔	3	3	2		0.5625
2	孔	3	2	0.5625
3	孔	1	3	3	2	0.5625		0		0

您可以使用聚合和HAVING来验证每个条件是否只有一个匹配项，而没有其他匹配项：

SELECT * 
FROM Part 
WHERE Section = 'C6x1_5/8x1/4' AND
Length BETWEEN 41.99 AND 42.01 
Part.Part_ID IN (SELECT f.Part_ID
FROM Fabrication f
GROUP BY f.Part_ID
HAVING SUM(CASE WHEN f.Type = 'HOLE' AND f.Face = 1 AND f.Side = 3 AND f.Offset = 2 AND f.Parameter_1 = 0.5625 THEN 1 ELSE 0 END) = COUNT(*) AND
SUM(CASE WHEN Location BETWEEN 2.99 AND 3.01 THEN 1 ELSE 0 END) = 1 AND
SUM(CASE WHEN Location BETWEEN 5.99 AND 6.01 THEN 1 ELSE 0 END) = 1 
);

看起来您的两个查询中唯一的差异是Location，您希望确保制造行的数量符合您的标准，并且没有任何超出您的标准的额外行。

您可以尝试使用条件聚合的一个选项：

select * 
from Part 
where Section = 'C6x1_5/8x1/4' 
and Length between 41.99 and 42.01 
and Part.Part_ID in (
select PART_ID
from Fabrication
where 
TYPE = 'HOLE' 
and FACE = 1 
and SIDE = 3 
and offset = 2 
and PARAMETER_1 = 0.5625
group by PART_ID
having Count(*)=Sum(case when LOCATION between 2.99 and 3.01 then 1 end) + Sum(case when LOCATION between 5.99 and 6.01 then 1 end) 
)