PostgreSQL：如何筛选对应指定fid行val一致的ID？

我有如下PostgreSQL数据表：

id        fid        val 
--------------------------
200       995        XXLL
200       996        XXLL
201       995        OOOP
201       996        OOOS
202       995        OKIL
202       996        OKIL
203       995        LLLL
203       996        CCCC

需要获取所有满足以下条件的id：

• 该id同时存在fid为995和996的两行记录
• 这两行的val值完全相同

预期输出为200、202，因为201和203对应的fid的val值不一致。

方法1：自连接查询 #

通过将表与自身连接，匹配相同id、不同fid且val相等的记录：

SELECT DISTINCT t1.id
FROM your_table t1
JOIN your_table t2 ON t1.id = t2.id
WHERE t1.fid = 995 
  AND t2.fid = 996 
  AND t1.val = t2.val;

方法2：分组聚合验证 #

先按id和val分组，筛选出同时包含995和996两个fid的组：

SELECT id
FROM your_table
WHERE fid IN (995, 996)
GROUP BY id, val
HAVING COUNT(DISTINCT fid) = 2;

方法3：窗口函数筛选 #

使用窗口函数计算每个id下val的唯一值数量，以及fid的覆盖情况：

WITH id_stats AS (
  SELECT 
    id,
    COUNT(DISTINCT fid) AS fid_count,
    COUNT(DISTINCT val) AS val_count
  FROM your_table
  WHERE fid IN (995, 996)
  GROUP BY id
)
SELECT id
FROM id_stats
WHERE fid_count = 2 AND val_count = 1;

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