select a.`CId`, sum(case when a.`score`>=70 and a.`score` <=80 then 1 else 0 end) from `SC` a group by a.`CId` 结果: