提取其中一列具有按id分组的空值的行

Question

我在Postgres 11中有以下表格

id          type1   type2                         type3                   code     
NCT00160290 Drug    lactulose                     lactulose               A05BA | A06AD
NCT00160290 Drug    plantago ovata                plantago ovata          (null)
NCT00251238 Drug    ginkgo biloba extract         ginkgo biloba extract   (null)

我想提取type3不为null但代码为null的所有行，但是如果任何id有任何type3的代码，我应该排除它。

我正在尝试以下查询

select distinct * 
from table
where type3 is not null 
  and code is null   --but this will include 'NCT00160290' which has a code
group by id

所需的输出为：

id          type1   type2                         type3                   code     
NCT00251238 Drug    ginkgo biloba extract         ginkgo biloba extract   (null)

Answer 1

您可以使用not exists

select t.*
from mytable t
where 
    t.type3 is not null
    and not exists (
        select 1 
        from mytable t1
        where t1.id = t.id and t1.code is not null
    )

此查询将利用(id, code)上的索引。

或者，您可以使用窗口函数：

select 
from (
    select t.*, bool_or(code is not null) over(partition by id) has_non_null_code
    from mytable t
) t
where not has_non_null_code