mysql - 选择特定字段没有其他记录的行

Question

大家好，我有下表:

ID - COL_A - COL_B

我需要检索所有 COL_A = 1 的记录，并且 COL_A = 1 的 COL_B 不得有其他 COL_A = 2 的记录。

你能帮我吗？

谢谢

Answer 1

这是一个不存在的情况，我认为:

SELECT *
FROM mytable AS t1
WHERE COL_A = 1 AND 
      NOT EXISTS (SELECT 1
                  FROM mytable
                  WHERE t1.COL_B = t2.COL_B AND COL_A = 2)  

您还可以使用LEFT JOIN:

SELECT t1.*
FROM mytable AS t1
LEFT JOIN mytable AS t2 ON t1.COL_B = t2.COL_B AND t2.COL_A = 2
WHERE t1.COL_A = 1 AND t2.COL_A IS NULL