SQL按条件过滤博客时保留全量关联分类聚合数据的方法

把分类过滤条件直接写在WHERE子句会在分组聚合前剔除所有不匹配条件的关联行，最终聚合时只能拿到命中过滤规则的单个分类，无法返回博客绑定的全部分类。

推荐用EXISTS子查询单独做过滤判断，完全不影响主查询的关联聚合逻辑，性能表现最好：

select b.id, b.domain, coalesce( array_agg(bc.name) 
    filter (where bc.id is not null), '{}' ) as cats
from blog b
left join blog_to_blog_category bt on bt.blog_id = b.id
left join blog_category bc on bc.id = bt.blog_category_id
where exists (
    select 1
    from blog_to_blog_category bt_filter
    join blog_category bc_filter 
        on bc_filter.id = bt_filter.blog_category_id
    where bt_filter.blog_id = b.id
      and bc_filter.name = 'marketing'
)
group by b.id;

逻辑说明 #

• EXISTS子查询仅负责判断当前博客是否绑定了marketing分类，不会修改主查询关联返回的结果集
• 主查询的左连和聚合逻辑和全量查询博客的逻辑完全一致，因此可以正常返回博客关联的所有分类
• 基于示例数据执行以上查询，只会返回匹配分类的id=1的博客one.com，对应的cats字段值为{business,marketing,misc}，完全符合预期。

如果追求代码简洁，也可以在分组后用HAVING做过滤，不需要额外写关联子查询：

select b.id, b.domain, coalesce( array_agg(bc.name) 
    filter (where bc.id is not null), '{}' ) as cats
from blog b
left join blog_to_blog_category bt on bt.blog_id = b.id
left join blog_category bc on bc.id = bt.blog_category_id
group by b.id
having 'marketing' = any(coalesce( array_agg(bc.name) 
    filter (where bc.id is not null), '{}' ));

该写法的缺点是需要先对所有博客做全量关联和聚合，再过滤掉不满足条件的记录，数据量大时性能比EXISTS方案差。

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