查询，只查找符合特定条件的行

Question

对于特定的同步&cb，我只需要这些记录如果所有的行都是not available。从下面我只需要Bal和BAL1的输出，因为所有的行都是not available

sync    cb        value
Bal    BAL1      not avaialble
Bal    BAL1      not available
Bal    BAL1      not available
Bal    BAL1      not available
order  OR1CNV0   abcdef
order  OR1CNV0   not available
order  OR1CNV0   not available
order  OR1CNV0   not available

Answer 1

一个聚合选项:

SELECT sync, cb
FROM yourTable
GROUP BY sync, cb
HAVING COUNT(CASE WHEN value <> 'not available' THEN 1 END) = 0;

Answer 2

如果您想要原始行，可以使用not exists:

select t.*
from t
where not exists (select 1
from t t2
where t2.sync = t.sync and t2.cb = t.cb and
t2.value <> 'not available'
);

Answer 3

如果您希望显示所有相关行而不分组，则可以使用窗口函数:

SELECT t.sync, t.cb
FROM (
SELECT *,
CountNA = COUNT(CASE WHEN t.value <> 'not available' THEN 1 END)
FROM yourTable t
) t
WHERE t.CountNA = 0;