在不知道确切的表结构的情况下，我假设status\u id和statusid引用同一列(如果它们是不同的列，我们需要在计数中使用statusid。）
根据预期的输出，您要计算状态标识并按项目任务标识分组。要确保每个任务都表示每个状态，首先，我们需要创建所有可能的项目任务标识/状态标识组合的子查询。然后我们将其与原始表的聚合值一起使用。

select
  ps.project_task_id,
  ps.status_id,
  count(w.status_id) as total
from (
  select distinct
      project_task_id,
      s.status_id
  from work_unit_status
  cross join (select distinct status_id from work_unit_status) s
  ) ps
left join work_unit_status w
    on ps.project_task_id = w.project_task_id and ps.status_id = w.status_id 
group by
    ps.project_task_id,
    ps.status_id

如果您真的需要硬编码从1到7的状态，请使用下面的查询。

select
  ps.project_task_id,
  ps.status_id,
  count(w.status_id) as total
from (
  select distinct
      project_task_id,
      s.status_id
  from work_unit_status
    cross join (
          select 1 as status_id
    union select 2
    union select 3
    union select 4
    union select 5
    union select 6
    union select 7
    ) s
  ) ps
left join work_unit_status w
  on ps.project_task_id = w.project_task_id and ps.status_id = w.status_id 
group by
  ps.project_task_id,
  ps.status_id
order by
  ps.project_task_id,
  ps.status_id

使用 in 你的案子呢

with t1 as (
 select project_task_id,
           status_id,
           sum(case when StatusID in (1,2,3,4,5,6,7) then 1 else 0) 
           as sum_s               
    from "Projects".work_unit_status

    group by project_task_id,status_id
              ) ,
           t2 as
             (
                 select * from (
                 select 1 as statusid 
                          union
                 select 2
                 union 
                 select 3
                 union
                 select 4 
                 union
                 select 5 
                 union
                 select 6
                  union
                select 7 ) t
             ) select t1.project_task_id,
                   t2.statusid,
                    case when t1.sum_s>0 or not null
                    then sum_s else 0 end as total
                  t2 full join t1 on t2.statusid=t1.status_id

尝试以下方法：使用all sum列的加法

select project_task_id,status_id,
isnull(sum(case when StatusID=1 then 1 else 0 end),0)+
isnull(sum(case whenStatusID=2 then 1 else 0 end),0) +
isnullsum(case when StatusID=3 then 1 else 0 end),0) +
isnullsum(case when StatusID=4 then 1 else 0 end),0)+
isnullsum(case when StatusID=5 then 1 else 0 end),0) +
isnullsum(case when StatusID=6 then 1 else 0 end),0) +
isnullsum(case when StatusID=7 then 1 else 0 end),0) as count_status
from"Projects".work_unit_status group by project_task_id,status_id