有一张表T-Score
Date Name Score
2008-8-8  拜仁 胜      
2008-8-9  奇才 胜      
2008-8-9  湖人 胜      
2008-8-10  拜仁 负
2008-8-8  拜仁 负      
2008-8-12  奇才 胜
要求输出下面格式：
Name 胜 负
拜仁  1  2
湖人  1  0
奇才  2  0      
注意：在字符串前加N，比如N'胜'
本人自己写的，但是感觉代码好长，是不是有更好更短的写法呢？求指导
select distinct Name,
(case Name
when N'拜仁' then (select count(*) from TableScore where Score='胜' and Name='拜仁')
when N'湖人' then (select count(*) from TableScore where Score='胜' and Name='湖人')
when N'奇才' then (select count(*) from TableScore where Score='胜' and Name='奇才')
end
) as 胜,
(case Name
when N'拜仁' then (select count(*) from TableScore where Score='负' and Name='拜仁')
when N'湖人' then (select count(*) from TableScore where Score='负' and Name='湖人')
when N'奇才' then (select count(*) from TableScore where Score='负' and Name='奇才')
end
) as 负
from TableScore

select Name,
Sum{
case Score
when  N'胜'  then  1
else  0
end
} as 胜,
Sum{
case  Score
when  N'负'  then  1
else  0
end
} as 负
from T_Scores
group by Name

哦，原来如此，这就等于胜了加一分，这样的道理。明白了

许庭洲 发表于 2013-6-8 11:22
select Name,
Sum{
case Score

谢谢！这样就清晰多了！
只要把大括号改成小括号就可以啦！:lol

select Name,COUNT((case score
when N'胜' then 1  end)) as 胜,
COUNT((case score
when N'负' then 1 end)) as 负
from T-Score
group by Name;