SQL来获取一列的值，从而使另一列中的某个值不存在

Question

我有下面的表

值

键
1	a
1	b
1	c
2	a
2	c

您可以这样做，方法是为每个键创建一个b值计数器，然后只选择具有0 b值的键：

SELECT 
key
FROM (SELECT
t.key, 
SUM(CASE WHEN t.value = 'b' THEN 1 ELSE 0 END) AS number_of_b_values
FROM t
GROUP BY t.key)
WHERE number_of_b_values = 0

试试这个

with b_keys as
(
select distinct key
from t
where value = 'b'
)
select distinct key
from t
left join b_keys
on t.key = b_keys.key
where b_keys.key is null

你真的在明确地寻找"b"；？

或者你真的需要找到丢失的钥匙吗；b"；但是"；a"；或"；c"；不见了？

如果您构建了一个所有预期值的列表，则可以将其与不存在一起使用，以识别所有缺少任何值的密钥：

with v as (
select distinct t.[key], dv.[value]
from (select distinct [value] from t) dv
cross join t
)
select [key]
from v
where not exists (select * from t where t.[value] = v.[value] and t.[key] = v.[key]);

参见演示