两表关联分组查询数量展示所有（没有记录的也显示0）

with n as (select * from base_nrfl nrfl  where nrfl.parentid=0)
 select n.nrflmc, nvl(a.nrflsl,0) as nrflsl,nvl(a.wbj,0) as wbj,nvl(a.ybj,0) as ybj from n
 left join
 (select SUBSTR (xfj.nrfldm,1,2) dmstr, COUNT(1) nrflsl ,  sum(decode(dcdb.DBBJBZ, 0, 1, 0)) wbj,
 sum(decode(dcdb.DBBJBZ, 1, 1, 0)) ybj
 from  yw_xfj xfj join yw_dcdb dcdb on dcdb.xfjbh = xfj.xfjbh and xfj.xfjzt > 0 and dcdb.STATE = 1
 GROUP BY SUBSTR (xfj.nrfldm, 1,2)) a
 on n.nrfldm=a.dmstr || ‘0000‘
 order by n.id

with n as (select * from jiangsu.sys_jg jg WHERE xianid = 320326 AND jg.status = 1 AND jg.JGXZ IN (0, 2) and jg.jgcj = 4)
select n.jgmc, nvl(a.sl,0) as ajsl from n
left join
(select aj.zrdwid,COUNT(1) sl from zdryk_ajxx aj GROUP BY aj.zrdwid) a
on n.jgid=a.zrdwid

两表关联分组查询数量展示所有（没有记录的也显示0）

原文：https://www.cnblogs.com/ZhangHaiBK/p/11635567.html