SQL MS Access中的唯一行查询

Question

我得到了以下条目:

Col_A  | Col_B  | Col_C
Foo    | Bar    | 1
Foo    | Blub   | 1
Foo    | Bar    | 1
Foo    | Blub   | 2
Foo    | Blub   | 2
Foo    | Bar    | 3
Foo    | Bar    | 3
Foo    | Bar    | 3
Foo    | Blub   | 4
Foo    | Bar    | 5
Foo    | Blub   | 5

我正在搜索所有Col_C值，在Col_B中只包含Blub。也就是说我想看

Col_A  | Col_B  | Col_C
Foo    | Blub   | 2
Foo    | Blub   | 4

Col_B中没有Bla项的行

目前我也收到

Foo    | Blub   | 1
Foo    | Blub   | 5

Answer 1

试试这个:

SELECT DISTINCT t1.*
FROM your_table t1
WHERE NOT EXISTS
(SELECT 1 
FROM your_table t2
WHERE t2.col_b <> 'Blub'
AND t1.col_a = t2.col_a
AND t1.col_c = t2.col_c)
AND t1.col_b = 'Blub'

参见SqlFiddle