我有一张 PostgreSQL 表：create table test(type_id, m_id)as values (1, 123),(2, 456),(3, 123),(4, 123),(2, 456),(1, 789);基本上，一个 m_id 可以有
我有一张 PostgreSQL 表：
create table test(type_id, m_id)as values
(1, 123)
,(2, 456)
,(3, 123)
,(4, 123)
,(2, 456)
,(1, 789)
;
基本上， m_id 该表中可能有多个与不同 type_id s 相关的行。
如何找到 m_id 仅具有值 的 type_id s 2 ？在此示例中，它 m_id 具有值 456 。
我尝试过按 分组 m_id ，但效果并不好。有什么想法吗？谢谢！
您的想法是正确的 group by 。只需添加 having clause 即可：
demo at db<>fiddle
select m_id from test
group by m_id having every(type_id=2);
| 中 |
|---|
| 456 |
every () / bool_and() 函数检查组中所有行的条件，以确保所有 type_id 出现给定的 m_id 都是 2 。或者，该组中不存在该表达式求值为 的行 false .
您还可以使用 exist :
select distinct m_id from test t1
where type_id=2
and not exists(select from test t2
where t1.m_id=t2.m_id
and t2.type_id<>2);
或者 anti-join :
select distinct t1.m_id
from test t1
left join test t2
on t1.type_id=2
and t2.type_id<>2
and t1.m_id=t2.m_id
--that's the `anti-` part:
--it's a `left join`, but you then ask only for unmatched rows
--marked by a null on the right
where t2.m_id is null
and t1.type_id=2;
或者 除外 :
select m_id from test where type_id=2
except
select m_id from test where type_id<>2;
下面是一个测试 ，其中 13 个这样 m_id 的 被埋在 900k 个随机行的堆中的 200k 个其他行中。它表明 exists 和显式 anti-join 示例都产生了一个 嵌套循环反连接 计划，其性能优于其他计划，仅为 3ms 。这要归功于这些 覆盖索引 :
create index on test(type_id,m_id);
create index on test(m_id)include(type_id);
Having 需要 253ms 并 except 采取 530ms ，即使他们也已获得足够的索引。