select D.DeptName as [Name], isnull (sum(1),0) as JibanjianNum from subdepartment sd left join service s on sd.subid=s.subid left join department d on sd.deid=d.deid where s.typeid=1 and s.useflag='Y' and (s.serviceid not in (select MainSerId from ServiceRelation)) group by D.DeptName
isnull (sum(1),0) as JibanjianNum
from subdepartment sd left join service s on sd.subid=s.subid
left join department d on sd.deid=d.deid
where s.typeid=1 and s.useflag='Y' and
(s.serviceid not in (select MainSerId from ServiceRelation))
group by D.DeptName