oracle统计一对多表，怎么用一条sql查询

Question

select  COUNT(distinct cs.id),COUNT(distinct fo.id),COUNT(distinct re.id),COUNT(distinct lo.id) from dh_project_saler ps
left join dh_crm_saleruser cs
on cs.saleropenid=ps.saleropenid
left join dh_crm_follow fo
on fo.saler_openid=ps.saleropenid
left join dh_crm_record re
on re.saler_open_id=ps.saleropenid
left join dh_crm_look lo
on lo.saler_openid=ps.saleropenid

where cs.createtime between '2021-09-26' and '2021-09-27' 
            and fo.operating_time between '2021-09-26' and '2021-09-27'
            and re.operating_time between '2021-09-26' and '2021-09-27'
            and lo.create_time between '2021-09-26' and '2021-09-27'
            and ps.number='HJ000069'

当这样子去查询的时候，如果最后一个统计为0的时候，前面的所有count都等于0了，怎么解决啊

Answer 1

首先，当 lo 没有与之匹配的数据时，count(lo.id) 为 0，那么，也就是说 lo.create_time 所有值都为 null，所以 lo.create_time between 的结果就是，没有任意行符合条件，从结构上来说，你的条件定义存在逻辑性问题

我用 mssql 写个类似的，你可以参考下

declare @bdate date,@edate date

select @bdate = '2021-09-26',@edate='2021-09-27'

select  COUNT(distinct cs_id)
    ,COUNT(distinct fo_id)
    ,COUNT(distinct re_id)
    ,COUNT(distinct lo_id) 
from (
    select  (case when cs.createtime between @bdate and @edate then cs.id else null end) as cs_id
        ,(case when fo.operating_time between @bdate and @edate then fo.id else null end) as fo_id
        ,(case when re.operating_time between @bdate and @edate then re.id else null end) as re_id
        ,(case when lo.createtime between @bdate and @edate then lo.id else null end) as lo_id
    from dh_project_saler ps
    left join dh_crm_saleruser cs on cs.saleropenid=ps.saleropenid
    left join dh_crm_follow fo on fo.saler_openid=ps.saleropenid
    left join dh_crm_record re on re.saler_open_id=ps.saleropenid
    left join dh_crm_look lo on lo.saler_openid=ps.saleropenid
    where ps.number='HJ000069'
) a

即：不管是否有关联数据，都不放到 where 中，而是用 case 进行判断，是否保留该行 id，在外层对所有保留的id进行统计

如有帮助，还望采纳