sql 成绩表 case then

select * from  dbo.tb_Scroe

select Name,(select count(*) from tb_Scroe where Name = t.Name and Scroe = '胜') as '胜'
,(select count(*) from tb_Scroe where Name = t.Name and Scroe = '负') as '负'
 from tb_Scroe as t group by Name;

select a.Name ,a.胜,b.负 from
 (select count(*) 胜,Name from tb_Scroe where Scroe='胜' group by Name ) a
 left join
 (select count(*) 负, Name from tb_Scroe where Scroe='负' group by Name ) b
  on a.Name=b.Name
 
 
  select Name , sum(case when Scroe = '胜' then 1 else 0 end) '胜',
  sum(case when Scroe = '负' then 1 else 0 end) '负'
  from tb_Scroe group by Name ;
 
 
    select Name ,(case when Scroe = '胜' then 1 else 0 end) '胜',
        (case when Scroe = '负' then 1 else 0 end) '负'
  from tb_Scroe group by Name ;

 

Scroe

ID    Name  Scroe     CreateDate

1      张三    胜          2014-06-09 00:00:00.000
2      张三    胜          2014-06-09 00:00:00.000
3      张三    负          2014-06-09 00:00:00.000
4      李四    负          2014-06-09 00:00:00.000
5      王五    负          2014-06-09 00:00:00.000
6      李四    负          2014-06-09 00:00:00.000
7     王五     胜          2014-06-09 00:00:00.000
9     张三     负          2014-06-09 00:00:00.000
8     王五     胜          2014-06-09 00:00:00.000
10    李四    胜          2014-06-09 00:00:00.000

 

 

 

结果：

姓名   胜  负

李四    1   2
王五    2   1
张三    2   2