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