if object_id('tb') is not null drop table tb create table tb (Num varchar(10),State varchar(10)) insert tb select 'A01','胜' union all select 'A01','胜' union all select 'A01','负' union all select 'A01','负' union all select 'A02','胜' union all select 'A02','负' union all select 'A02','负' select Num, 胜 = sum(case when (tb.State = '胜') then 1 else 0 end), 负 = sum(case when (tb.State = '负') then 1 else 0 end) from tb group by Num
