如何在PostgreSQL中筛选仅关联type_id=2的m_id

这里提供几种在PostgreSQL中实现需求的可行SQL写法：

方法一：分组聚合筛选 #

通过分组后对比总记录数与type_id=2的记录数，确保两者相等，即该m_id的所有记录type_id都是2：

SELECT m_id
FROM test
GROUP BY m_id
HAVING COUNT(*) = COUNT(CASE WHEN type_id = 2 THEN 1 END);

方法二：排除法查询 #

先筛选出关联过type_id=2的m_id，再排除那些存在其他type_id的m_id：

SELECT DISTINCT m_id
FROM test
WHERE type_id = 2
AND m_id NOT IN (
    SELECT m_id
    FROM test
    WHERE type_id != 2
);

方法三：窗口函数筛选 #

利用窗口函数计算每个m_id的不同type_id数量，同时判断是否包含type_id=2，最终筛选出仅有一种type_id且为2的记录：

SELECT DISTINCT m_id
FROM (
    SELECT 
        m_id,
        COUNT(DISTINCT type_id) OVER (PARTITION BY m_id) AS type_count,
        BOOL_OR(type_id = 2) OVER (PARTITION BY m_id) AS has_type_2
    FROM test
) t
WHERE type_count = 1 AND has_type_2;

内容的提问来源于stack exchange，提问作者Fisher Coder