您可以在此处使用多个查询，例如：

Select distinct id from 
    (SELECT id,status,
   sum(count) as count 
   FROM `test` where count=1 group by status order by id 
    )

使用子查询尝试以下操作：

select t2.id, b.status, t2.scount 
from 
(SELECT id,max(scount) as scount 
FROM
(
   SELECT id,status,sum(count) as scount 
     FROM t where count=1
     group by status,id
)a group by id) t2
inner join 
(
    SELECT id,status,sum(count) as scount 
     FROM t where count=1
     group by status,id

)b on t2.id =b.id and t2.scount=b.scount

使用相关子查询

select min(status) status,id,s as count from
(
select * from 
(
select id,status,sum(cnt) s from t 
 where cnt>0
 group by id,status
) as  t1 where t1.s in( select max(s) from 
                          (select status,id,sum(cnt) s from t
                           where cnt>0
                            group by
                          status,id) t2 where t1.id=t2.id
                           group by t2.id
                       )
                       ) as t3 group by id,s

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f15f06ce7e6967e1f6c1d21c830b3985

status  id  count
test1   1   2
exam1   2   2
mit1    3   1
term1   4   1

这是个棘手的问题，因为有两个条目 id 3具有相同的 count . 不过，我认为这个查询可以满足您的要求：

SELECT c1.id, MIN(c1.status) AS status, c1.sum_c
FROM (SELECT id, status, SUM(`count`) AS sum_c 
      FROM `counts` 
      GROUP BY id, status
     ) c1
JOIN (SELECT id, MAX(sum_c) AS max_c
      FROM (SELECT id, status, SUM(`count`) AS sum_c 
            FROM `counts` 
            GROUP BY id, status
           ) c1
      GROUP BY id) c2
ON c2.id = c1.id AND c2.max_c = c1.sum_c
GROUP BY c1.id

输出：

id  status  sum_c
1   test1   2
2   exam1   2
3   mit1    1
4   term1   1