sql - "WHERE (subquery) IN (subquery)"可能吗？

Question

我们需要选择只使用连接到机器的标签的测试。

• 标签对测试来说是多对多的。 (TagTest关联表)

• 标签对机器来说是多对多的。 (TagMachine关联表)

例子:

• 如果测试有标签 [A,B,C] 而机器有 [A,B,D] 则不应选择测试，因为它的标签不是机器标签的子集。

• 如果测试有标签 [A,B] 并且机器有 [A,B,D] 则应该包含测试。

• 如果测试没有标签，则应始终包含它。

像这样的构造应该可以工作:

SELECT *
FROM Test te
WHERE 
    (SELECT tt.tagId
    FROM TagTest tt 
    WHERE tt.testId = te.Id)
 IN
    (SELECT tm.tagId
    FROM TagMachine tm
    WHERE tm.machineId = 123)

但是这种查询可能吗？如果没有，怎么可能达到预期的效果？

Answer 1

IN() 无法自行完成此操作。您可以制作两个 CTE 并将它们连接在一起，但这仍然有点棘手。

相反，让我们来扭转这个问题。我们可以查找缺少任何一个必需标签 的记录，而不是查找匹配所有 良好标签的记录。从问题的第一个示例([A,B,C] vs [A,B,D])，我们正在寻找 TestTag 记录带有 C 标签。一旦我们有了这些信息，我们就可以在子查询中使用它来排除所有具有出现在这些结果中的 Id 的 Test 记录。

因此，首先要做的是使用排除连接来查找缺少相应 TagMachine 记录的 TestTag 结果:

SELECT tt.testId, tt.tagId
FROM TestTag tt 
LEFT JOIN TagMachine tm ON tm.machineId = 123 AND tm.tagId = tt.tagId
WHERE tm.tagId IS NULL

上述查询结果中任何 testId 的存在都会使具有该 Id 的 Test 不合格 ... 但我们确实需要所有其他 测试 记录。所以现在只需将其限制为 DISTINCT testId 并将其用作任何排除连接、NOT IN() 或 NOT EXISTS() 中的子查询。选择:

SELECT * 
FROM Tests
WHERE Id NOT IN (
     --identify tests hat are missing at least one tag
     SELECT DISTINCT tt.testId 
     FROM TestTag tt 
     LEFT JOIN TagMachine tm ON tm.machineId = 123 AND tm.tagId = tt.tagId
     WHERE tm.tagId IS NULL)