mysql left join 查询时主表为null统计count为0的解决方法(join后面加group by)

 

如果没有加group by 则会出UserCount为0外其它都是null

select a.*,count(b.ID) as UserCount from erp_role as a 
    left join erp_user as b on a.ID=b.RoleId where 1=1 and a.TenantID=2 and a.RoleName='string'

 

加上group by一切正常

select a.*,count(b.ID) as UserCount from erp_role as a 
    left join erp_user as b on a.ID=b.RoleId where 1=1 and a.TenantID=2 and a.RoleName='string' GROUP BY a.ID