没有其他记录特定字段中选择行

Question

Ciao的大家，我有如下表：

ID - 为col_a - COL_B

我需要检索所有具有记录为col_a = 1 COL_A = 1的COL_B不得有COL_A = 2的其他记录。

你能帮助我吗？

感谢

Answer 1

这是一个案例NOT EXISTS我想：

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