按状态统计父记录 新增含指定子状态的父记录统计需求

核心思路 #

利用EXISTS子查询快速判定父记录是否存在状态为'a'的子元素，结合CASE语句筛选符合条件的父ID，最后通过COUNT(DISTINCT)完成去重统计。

修改后的完整SQL #

select count(distinct parent.id)                                             as all,
       count(distinct parent.id) filter (where parent.status = 'CREATED')    as new,
       count(distinct parent.id) filter (where parent.status = 'SIGN')       as sign,
       count(distinct parent.id) filter (where parent.status = 'SENT')       as send,
       
       -- 新增统计项：统计至少有一个子元素状态为'a'的父记录
       count(distinct case 
                        when exists (select 1 from child where child.parent_id = parent.id and child.status = 'a')
                        then parent.id 
                      end) as with_child_element 
from "parent";

代码说明 #

• EXISTS子查询：针对每条父记录，检查是否存在匹配的子记录（父ID关联且子状态为'a'），找到匹配项后立即停止检索，性能高效。
• CASE语句：仅当父记录满足条件时返回其ID，否则返回NULL。
• COUNT(DISTINCT)：自动忽略NULL值，同时对重复的父ID只计数一次，完全符合“同一父记录仅计一次”的需求。

替代实现方式 #

也可以用SUM(DISTINCT)实现，逻辑类似：

select count(distinct parent.id)                                             as all,
       count(distinct parent.id) filter (where parent.status = 'CREATED')    as new,
       count(distinct parent.id) filter (where parent.status = 'SIGN')       as sign,
       count(distinct parent.id) filter (where parent.status = 'SENT')       as send,
       
       sum(distinct case 
                     when exists (select 1 from child where child.parent_id = parent.id and child.status = 'a')
                     then 1 
                     else 0 
                   end) as with_child_element 
from "parent";

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