如何在 PostgresQL 中使用 NULL 归档行

Question

我正在连接很多表，我有一个结果集，如下所示：

  ID1 ID2 ID3
  -----------
  1   1   1
  1   1   NULL
  1   2   1
  1   2   2
  1   2   NULL
  1   3   NULL

仅当存在 ID3 和 ID2 相同的另一行时，我才想忽略 ID3=NULL 的行，因此结果集应如下所示：

  ID1 ID2 ID3
  -----------
  1   1   1
  1   2   1
  1   2   2
  1   3  NULL

我希望使用一些不需要嵌套查询的聪明方法，但我还没有找到任何方法。 用于过滤结果集中的 ID3 值的 DISTINCT、MAX 等尚未成功。我正在联接大约十几个表来获取结果集，因此提供源表定义有点困难。 有人有建议吗？

提前致谢

Answer 1

您可以使用窗口函数：

select t.*
from (select t.*, max(t.id3) over (partition by id1, id2) as maxid3
      from t
     ) t
where maxid3 is null or id3 is not null;

Answer 2

您可以将 OR 与 EXISTS 子句一起使用：

SELECT m.ID1,
    m.ID2,
    m.ID3
FROM MyTable m
WHERE m.ID3 IS NOT NULL
    OR (m.ID3 IS NULL
        AND NOT EXISTS (SELECT 1 FROM MyTable s WHERE s.ID1 = m.ID1 AND s.ID2 = m.ID2 AND s.ID3 IS NOT NULL)
    )

Answer 3

带有计数的窗口函数的其他变体更明确地说明了您选择的条件：

SELECT ID1, ID2, ID3 
FROM (
 SELECT 
  t.*,
  COUNT(ID3) OVER(PARTITION BY (ID1, ID2)) AS n
 FROM myTable t
) ot 
WHERE NOT (n > 0 AND ID3 IS NULL);

Answer 4

另一种方法：

with t(i1,i2,i3) as (values(1,1,1),(1,1,null),(1,2,1),(1,2,2),(1,2,null),(1,3,null))
select
  i1,i2,
  unnest(coalesce(array_agg(i3) filter (where i3 is not null),array[null]::int[]))
from t
group by i1,i2;