如何编写SQL查询符合OS状态条件但S状态未设为4的SID

需求回顾 #

需要找出STable中满足以下条件的SID：

• S.StatusID != 4（结合补充说明，实际只需关注S.StatusID = 3的记录）
• 关联的OSTable中所有OS.StatusID都属于{4, 6, 7, 8}

原SQL的问题 #

你当前的SQL逻辑完全偏离需求：

1. HAVING条件错误：原语句要求OS表必须同时存在4、6、7、8四种状态，而需求是只要所有OS状态都在这四个范围内即可，不需要全部覆盖。
2. 分组字段错误：GROUP BY用了S.SubmissionID，但查询目标是SID，如果SubmissionID和SID不是一一对应，会导致分组混乱，结果不准确。

正确SQL写法 #

写法一：GROUP BY + HAVING统计排除法

SELECT S.SID
FROM STable S
JOIN OSTable OS ON S.SID = OS.SID
WHERE S.StatusID = 3
GROUP BY S.SID
HAVING COUNT(CASE WHEN OS.StatusID NOT IN (4, 6, 7, 8) THEN 1 END) = 0

逻辑：按SID分组后，统计所有不符合状态的记录数，等于0就说明该SID下所有OS状态都符合要求。

写法二：NOT EXISTS子查询（更高效）

SELECT S.SID
FROM STable S
WHERE S.StatusID = 3
AND NOT EXISTS (
    SELECT 1
    FROM OSTable OS
    WHERE OS.SID = S.SID
    AND OS.StatusID NOT IN (4, 6, 7, 8)
)

逻辑：直接检查当前SID是否存在不符合条件的OS记录，不存在则说明所有OS状态都合规，符合筛选要求。

内容的提问来源于stack exchange，提问作者Tony