SQL如何在COUNT统计后对指定角色的计数结果进行求和

方案1：仅输出合并后的统计结果（不保留TimeTrackingWritingMember、Member单独条目） #

直接在分组前通过CASE语句完成角色名称映射，再分组统计即可：

SELECT 
  CASE 
    WHEN roles.Name IN ('TimeTrackingWritingMember', 'Member') THEN 'Basic User'
    ELSE roles.Name 
  END AS Role_Name,
  COUNT(DISTINCT ur.UserId) AS RoleCount
FROM dbo.AbpUsers as users 
LEFT JOIN resourcing.CompanyorganizationUnitUserRole AS ur on users.Id = ur.UserId
LEFT JOIN dbo.AbpRoles AS roles ON roles.Id = ur.RoleId
GROUP BY 
  CASE 
    WHEN roles.Name IN ('TimeTrackingWritingMember', 'Member') THEN 'Basic User'
    ELSE roles.Name 
  END;

执行后会得到如下格式的结果：

方案2：保留原有所有角色明细，同时新增Basic User合计条目 #

通过UNION ALL拼接原有明细和合并统计结果即可：

-- 原有角色明细统计
SELECT roles.Name AS Role_Name , COUNT(DISTINCT ur.UserId ) AS RoleCount 
FROM dbo.AbpUsers as users 
LEFT JOIN resourcing.CompanyorganizationUnitUserRole AS ur on users.Id = ur.UserId
LEFT JOIN dbo.AbpRoles AS roles ON roles.Id = ur.RoleId
GROUP BY roles.Name

UNION ALL

-- 新增Basic User合并统计
SELECT 'Basic User' AS Role_Name, COUNT(DISTINCT ur.UserId) AS RoleCount
FROM dbo.AbpUsers as users 
LEFT JOIN resourcing.CompanyorganizationUnitUserRole AS ur on users.Id = ur.UserId
LEFT JOIN dbo.AbpRoles AS roles ON roles.Id = ur.RoleId
WHERE roles.Name IN ('TimeTrackingWritingMember', 'Member')

注意事项 #

如果存在用户同时拥有TimeTrackingWritingMember和Member两个角色的情况，上述写法统计的是去重后的用户总数，比直接相加原有两个角色统计值的结果更准确。

内容的提问来源于stack exchange，提问作者Samuel Van Bladel