我有一张 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
,即使他们也已获得足够的索引。