group和distinct之间的冲突

select distinct sp.ID,sp.Name,sp.No,sy.Name as CityName, count(emp.EmployeeID) as EmpNum from Sp_ServiceProvider sp
inner join Sys_Area sy on sp.RegionID = sy.ID
left join Rep_Account A on sp.id = A.ServiceProviderID
left join Rep_InsuranceSolution so on A.ID = so.AccountID
left join Emp_EmployeeSocialFundlInfo emp on so.ID = emp.SolutionID and emp.IsLatest = 1 
and (emp.EndTaskStatus is null or emp.EndTaskStatus not in (3,5))
where sp.Status = 0 and sp.Name like '%贵阳%'
group by sp.ID,sp.Name,sp.No,sy.Name

结果：
ID    Name    No    CityName    EmpNum
A7DA54DE-9CD1-4A60-83C2-0B1EC420CDB4    贵阳市外国企业服务总公司    S000001P000039    贵阳市    6

去掉distinct和group by

select   sp.ID,sp.Name,sp.No,sy.Name as CityName,  emp.EmployeeID  from Sp_ServiceProvider sp
inner join Sys_Area sy on sp.RegionID = sy.ID
left join Rep_Account A on sp.id = A.ServiceProviderID
left join Rep_InsuranceSolution so on A.ID = so.AccountID
left join Emp_EmployeeSocialFundlInfo emp on so.ID = emp.SolutionID and emp.IsLatest = 1 
and (emp.EndTaskStatus is null or emp.EndTaskStatus not in (3,5))
where sp.Status = 0 and sp.Name like '%贵阳%'

结果：

ID    Name    No    CityName    EmployeeID
A7DA54DE-9CD1-4A60-83C2-0B1EC420CDB4    贵阳市外国企业服务总公司    S000001P000039    贵阳市    30BDE0EE-06B6-4D89-A03B-15AFD26C0888
A7DA54DE-9CD1-4A60-83C2-0B1EC420CDB4    贵阳市外国企业服务总公司    S000001P000039    贵阳市    30BDE0EE-06B6-4D89-A03B-15AFD26C0888
A7DA54DE-9CD1-4A60-83C2-0B1EC420CDB4    贵阳市外国企业服务总公司    S000001P000039    贵阳市    30BDE0EE-06B6-4D89-A03B-15AFD26C0888
A7DA54DE-9CD1-4A60-83C2-0B1EC420CDB4    贵阳市外国企业服务总公司    S000001P000039    贵阳市    30BDE0EE-06B6-4D89-A03B-15AFD26C0888
A7DA54DE-9CD1-4A60-83C2-0B1EC420CDB4    贵阳市外国企业服务总公司    S000001P000039    贵阳市    30BDE0EE-06B6-4D89-A03B-15AFD26C0888
A7DA54DE-9CD1-4A60-83C2-0B1EC420CDB4    贵阳市外国企业服务总公司    S000001P000039    贵阳市    30BDE0EE-06B6-4D89-A03B-15AFD26C0888

可得知：

如果group和distinct在同一执行等级上的话，group by的优先级大于distinct

所以sql需要修改为：

select  ID, Name, No, CityName, count(EmployeeID) AS EmpNum from
(select distinct sp.ID,sp.Name,sp.No,sy.Name as CityName, emp.EmployeeID  from Sp_ServiceProvider sp
inner join Sys_Area sy on sp.RegionID = sy.ID
left join Rep_Account A on sp.id = A.ServiceProviderID
left join Rep_InsuranceSolution so on A.ID = so.AccountID
left join Emp_EmployeeSocialFundlInfo emp on so.ID = emp.SolutionID and emp.IsLatest = 1 
and (emp.EndTaskStatus is null or emp.EndTaskStatus not in (3,5))
where sp.Status = 0   and sp.Name like '%贵阳%') T
group by  ID, Name, No, CityName 

结果：

ID    Name    No    CityName    EmpNum
A7DA54DE-9CD1-4A60-83C2-0B1EC420CDB4    贵阳市外国企业服务总公司    S000001P000039    贵阳市    1

 --20201231修改：count(distinct employeeid)

select  sp.ID,sp.Name,sp.No,sy.Name as CityName, count(distinct emp.EmployeeID)  from Sp_ServiceProvider sp
inner join Sys_Area sy on sp.RegionID = sy.ID
left join Rep_Account A on sp.id = A.ServiceProviderID
left join Rep_InsuranceSolution so on A.ID = so.AccountID
left join Emp_EmployeeSocialFundlInfo emp on so.ID = emp.SolutionID and emp.IsLatest = 1 
and (emp.EndTaskStatus is null or emp.EndTaskStatus not in (3,5))
where sp.Status = 0   and sp.Name like '%贵阳%'
group by sp.ID,sp.Name,sp.No,sy.Name